SQL contains/exists matching data, something as LIKE operator

I am trying to figure out a way in SQL to match a comma separated field with another value.
For example.

select * from tbl_test where fld_search like '%Canton, Ohio, Biology , Chemistry%

In the database I have the word "Biology"
Now, the statement above doesn't work at all.
Any way to compare two fields ?

Thanks much.
Who is Participating?
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
You should use IN operator for this

select * from tbl_test where fld_search IN ('Canton', 'Ohio', 'Biology' , 'Chemistry')

Hope this helps
Patrick MatthewsCommented:
The easiest thing to do would be to load up a temp table, and then join to it:

CREATE TABLE #tmp (SearchTerm varchar(1000))
INSERT INTO #tmp (SearchTerm) VALUES ('Canton')
INSERT INTO #tmp (SearchTerm) VALUES ('Ohio')
INSERT INTO #tmp (SearchTerm) VALUES ('Biology')
INSERT INTO #tmp (SearchTerm) VALUES ('Chemistry')

FROM tbl_test t1 INNER JOIN
      #tmp t2 ON t1.fld_search LIKE '%' + t2.SearchTerm + '%'


That can be put into an sproc, to make it easier to run from PHP...
The % is a wild card so your query is looking for any data string that has the exact phrase

"Canton, Ohio, Biology , Chemistry"

In it.  To search for just the word "biology" you need to say;


You can also search for things like this:


which will return anything that has all 3 words, in that order.

Another option for searching for partial strings is this:

where charindex('Biology', MyTextField) > 0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

If it is your database field that is comma separated and contains multiple values, you may be looking for something like:

SELECT * FROM tbl_test
WHERE fld_search LIKE '%Canton%'
OR fld_search LIKE '%Ohio%'
OR fld_search LIKE '%Biology%'
OR fld_search LIKE '%Chemistry%'
martyjeAuthor Commented:
Thanks much for the quick responds.
IN the statement:
select * from tbl_test where fld_search like '%Canton, Ohio, Biology , Chemistry%

"Canton, Ohio, Biology , Chemistry" data is coming from another table. It's one field. have data separated with commas.

Any thoughts?
Thanks ya all.
I think the OR statement I put above should work - did you try it?
Scott PletcherSenior DBACommented:
You can do this as a "quick and dirty" way to make it work, although it does give poor performance vs other methods.

select *
from tbl_test
where CHARINDEX(',' + RTRIM(fld_search) + ','  ,  ',' + 'Canton, Ohio, Biology, Chemistry' + ',') > 0
martyjeAuthor Commented:
I changed , to ', ' in my data and used ur logic above and am back in business. thanks.
NerdsOfTechTechnology ScientistCommented:
1.) You should "READ" the "COMMA SEPERATED VALUES" in to an array via PHP implode
2.) Loop throught the array and make an SQL with individual LIKE = '%value%' lines
3.) Execute SQL statement
4.) Check data
5.) Output results

I'll Post the code next
NerdsOfTechTechnology ScientistCommented:
Nevermind your answer was found. Solution canceled.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.