?
Solved

SQL contains/exists matching data, something as LIKE operator

Posted on 2009-03-31
10
Medium Priority
?
789 Views
Last Modified: 2013-12-12
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.
0
Comment
Question by:martyje
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +4
10 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 24029663
You should use IN operator for this

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

Hope this helps
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24029691
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')

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

DROP TABLE #tmp




That can be put into an sproc, to make it easier to run from PHP...
0
 
LVL 15

Expert Comment

by:MNelson831
ID: 24029692
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;

like('%biology%')

You can also search for things like this:

Like('%biology%ohio%chemistry')

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
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 5

Expert Comment

by:catherinelouise
ID: 24029735
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%'
0
 

Author Comment

by:martyje
ID: 24029885
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.
0
 
LVL 5

Expert Comment

by:catherinelouise
ID: 24029909
I think the OR statement I put above should work - did you try it?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 24030824
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
0
 

Author Closing Comment

by:martyje
ID: 31564864
I changed , to ', ' in my data and used ur logic above and am back in business. thanks.
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24031206
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
=NerdsOfTTech
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24031260
Nevermind your answer was found. Solution canceled.
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question