martyje
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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%chemis try')
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
"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%chemis
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
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%'
SELECT * FROM tbl_test
WHERE fld_search LIKE '%Canton%'
OR fld_search LIKE '%Ohio%'
OR fld_search LIKE '%Biology%'
OR fld_search LIKE '%Chemistry%'
ASKER
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.
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?
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
select *
from tbl_test
where CHARINDEX(',' + RTRIM(fld_search) + ',' , ',' + 'Canton, Ohio, Biology, Chemistry' + ',') > 0
ASKER
I changed , to ', ' in my data and used ur logic above and am back in business. thanks.
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
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
Nevermind your answer was found. Solution canceled.
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...