CODEWITHSUNDEEP
asp.netsql
w0051977
w0051977

Reputation: 15787

SQL LIKE - exact match

Please see the database table below, which I have created to explain my question:

CREATE Table Test(id int not null IDENTITY,data varchar(100))
INSERT INTO Test(data) values ('Simon')
INSERT INTO Test(data) values ('David;James;Michael')

Please see the SQL statement below:

select *
from Test
where data like '%_James%'
    or data like '%James;%'

I believe that James will always be found regardless of whether there is one name or multiple names in the table cell, assuming that semi colons are always used as the delimiter.

Another developer said that he would load the values into an array (in ASP.NET or whatever programming language) and loop through the array to find an exact match, however I don't believe this step is necessary as 'James' will always be found. Is this step necessary?

Upvotes: 0

Views: 1083

Answers (3)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171351

It is better to filter in the query. What if there were millions of records? You wouldn't want to retrieve all of this first, just to filter.

Assuming you have to stick with this denormalized schema, I would do it like this. The first two cases can make use of an index on the data column. Assumption is that data never has a leading or trailing semicolon:

select * 
from Test 
where data = 'James'
    or data like 'James;%'
    or data like '%;James;%' 
    or data like '%;James'

Upvotes: 2

Giscard Biamby
Giscard Biamby

Reputation: 4609

Using the SQL you pasted, james will be found if there is at least one character before it (e.g., "2James", "BJames", "2Jamesey" would match), or if there is a semicolon after "james" (e.g., "2James;", "BJames;", "James"). But "James" by itself would not match because you used the underscore.

It isn't clear exactly what you are trying to do. Do you only want to find rows where there is an exact match for the word "James" (with "word" boundaries before and after "James", or would you want rows where "James" appears anywhere in the column, even if it's part of a larger word, e.g., "Jamestown"). You could fix by adding or data = 'James'. I'm not sure if the underscore thing is doing what you want though, since I'm not sure what your overall goal is. You may or may not need further changes to the query.

Depending on how confident you are that the data in the table is clean, you could write a statement to do what you say. I would keep the search inside the SQL statement rather than pull records out and loop through values in an array, since that could become inefficient in some cases.

Upvotes: 1

MiMo
MiMo

Reputation: 11953

No, loading the values in an array is not necessary - and it would be very bad for performance in any case.

Having said that you SQL is a bit strange - if you want to insert the names separated by ; I think you need:

INSERT INTO Test(data) values ('David;James;Michael')

Probably it would be even better if you make sure that every name starts and ends with the separator:

INSERT INTO Test(data) values (';David;James;Michael;')

Then you can use

SELECT * FROM Test WHERE data like '%;James;%'

and be sure to always get the correct result.

Last but not least, stuffing multiple valuers in a single column can be done, but it is not the best thing to do in a SQL database, it would be better to keep the names in separate rows in a different table and then have a cross-reference to it (normalizing the structure).

Upvotes: 1

Related Questions