matching word in a string

Question

0.00/5 (No votes)

See more:

i have a column('Specialities') in that so many specialities added and they are separated by comma(,) (like: Cardiology,eye,MRI,.....etc). if user selects 'speciality' i want to search if that string contains this 'speciality' whole row should comes up if not no row comes up... please help me out in this

thanks in advance

Posted 3-Oct-12 21:27pm

chetan B Y

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2012-10-03T21:33:00

Solution 1

Try:

SQL

SELECT * FROM myTable WHERE Specialities LIKE '%,word,%'

Clearly, you want to replace "word" with the keyword of your choice.

Posted 3-Oct-12 21:33pm

OriginalGriff

Comments

damodara naidu betha 4-Oct-12 6:25am

This query works if the column values contains the word starts with ',' and ends with ','. For example assume that the column contains '200,300,400'. It doesn't return row if we replace the "word" with "200". It returns row if we place with 300 instead of 200.

OriginalGriff 4-Oct-12 6:46am

It was an example - you will probably want to expand it to fit your system!
(If I spelled out every possibility, it would hide the essential detail of how LIKE works)

Maciej Los 4-Oct-12 15:36pm

It's a good example ;)
+5!

damodara naidu betha · Answer 2 · 2012-10-05T21:24:00

Hi ,

I have the following in data in "MyTable".

SQL

Id  Values
-------------------
1   2000,3000,4000
2   3000,4000,5000
3   6000,5000,6000

If I run the following queries..

SQL

SELECT * FROM MyTable WHERE Values LIKE '%,2000,%'

-- result is nothing even first row contains 2000, because column is value is not
-- started with ','

SELECT * FROM MyTable WHERE Values LIKE '%,3000,%'
-- it returns first row of the table instead of returning two rows from the table.
-- because of the same reason , 3000 is not started with ',' in the second row
-- column

So here LIKE '%,word,%' doesn't work. I think the solution for this question is bit complex. Here is the sample code...

SQL

DECLARE @SearchTXT VARCHAR(MAX)= '3000'; 

WITH Namescte AS (
    SELECT  Id,CAST('<i>' + REPLACE([Values], ',', '</i><i>') + '</i>' AS XML) AS [Values]
    FROM MyTable
)
,SplitedCTE AS (SELECT 
   ID,ROW_NUMBER() OVER(PARTITION BY ID,x.i.value('.', 'VARCHAR(10)') ORDER BY ID) ROWNUM,
   x.i.value('.', 'VARCHAR(10)') AS Value
FROM Namescte
CROSS APPLY [Values].nodes('//i') x(i))

SELECT MT.* FROM MyTable MT JOIN SplitedCTE SC ON MT.Id = SC.Id
WHERE ROWNUM < 2 AND SC.Value = @SearchTXT