Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have table data like below
Id | StringValue 
== | ============= 
1  | 4,50  
2  | 90,40

I will get input StringValue like 4. I need to fetch the data exact matched record. When i am using like operator, select query giving two rows but i need exact matched data record only.

can anybody please help me on this?

What I have tried:

SQL
SELECT * FROM Table1 where StringValue like '%4%'
but it returns two rows both ID 1 and 2.

My expectation is I need to get ID = 1 row only
Posted
Updated 9-Jan-23 2:16am
v2
Comments
Jörgen Andersson 17-Jan-23 8:16am    
Are those values comma separated numbers or numbers with decimal comma?

Both string values contain the character 4 - row 1 starts with "4,", and row 2 ends with ",40".

If you want to use Like for an exact match, you need to account for four possible positions: the start of the string, the middle of the string, the end of the string, or the entire string:
SQL
SELECT * 
FROM Table1 
WHERE StringValue Like '4,%'   /* Start of the string */
   OR StringValue Like '%,4,%' /* Middle of the string */
   OR StringValue Like '%,4'   /* End of the string */
   OR StringValue = '4'        /* Only item in the string */
This will not be particularly efficient, but it should be marginally better than splitting the StringValue for every row.

But as Dave already pointed out, this is a poor database design. Trying to stuff multiple values into a single row will always cause problems. If possible, you should normalize your database.
Database normalization description - Office | Microsoft Learn[^]
 
Share this answer
 
v2
To add to what Dave has said...

The LIKE operator only works with strings: so even if you corrected your mistake and store numbers in numeric columns - which you really must do - SWL would convert them back to strings to do a LIKE comparison.

But even then, there is no built in operator that will compare the numbers 4.5 and 4 and give you a "these are identical" result.
Your choices (once your DB design is corrected) are to convert the numbers to integers using the SQL Server CAST() Function[^] which will discard any fractional part, or use a range:
SQL
...WHERE x BETWEEN 4.0 AND 4.99999


You must change your DB first though: Always use the appropriate datatype for storage. Numbers in numeric fields (INT, FLOAT, DECIMAL). Dates in date based columns (DATE, DATETIME, DATETIME2). And you need to modify your presentation software to work with them - which means validating and converting inputs before INSERTing or UPDATEing the DB by passing the converted values as parameters.

If you don't, you are storing up some major problems for yourself: invalid data, mixed format data, and SQL Injection are the major worries.
 
Share this answer
 
Well, since you made the mistake of storing the string instead of splitting the string and storing the numbers, you're going to have to split the string in the SQL with STRING_SPLIT[^], then look in that returned array for the value you're looking for.

The problem with this is your query is going to have to execute STRING_SPLIT ON EVERY ROW IN THE TABLE so you can search the result for the value. If you have a large number of records in the table, this is going to take quite a long time.

I HIGHLY suggest you rethink how you're representing this data and break it up into tables and relations that take advantage of SQL's strengths.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900