Query find occurence of string

Question

Query find occurence of string

SQL_Surfer

SSCrazy Eights

Points: 8320
More actions
April 4, 2012 at 4:27 am

#266864

I have a comma seperated list of strings like 'abc, efg, ijk, lmn'. I need to return true if string 'efg' contains in my list of string. In this case it does. How do I solve this?
Thanks for your help.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 1

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 2

Its not pretty and there are better ways but without more information it is difficult to provide a solution.

CREATE TABLE #temp

(TextValue VARCHAR (5))

INSERT INTO #temp

SELECT 'efg' UNION ALL

SELECT 'abc' UNION ALL

SELECT 'ijk' UNION ALL

SELECT 'Imn' UNION ALL

SELECT 'aaa'

SELECT CASE WHEN TextValue LIKE '%efg%' OR TextValue LIKE '%abc%' OR TextValue Like '%ijk%' OR TextValue Like '%Imn%' THEN 'True' ELSE 'False' END

FROM #temp

DROP TABLE #temp

Cheers

Andy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

SQL_Surfer SSCrazy Eights Points: 8320 More actions · Answer 3

PATINDEX is not going to work. And temp table solution is also not going to work. Since, those values are (list of strings) are in table and can contain any number of strings in it.

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 4

SQL_Surfer (4/4/2012)
PATINDEX is not going to work. And temp table solution is also not going to work. Since, those values are (list of strings) are in table and can contain any number of strings in it.

What exactly do you mean with list of strings in a table?

Can you give DDL of the table together with sample data? (read the first link in my sig on how to do that)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 5

SQL_Surfer (4/4/2012)
PATINDEX is not going to work. And temp table solution is also not going to work. Since, those values are (list of strings) are in table and can contain any number of strings in it.

The Temp table was not the solution - it was there to provide my query some data to run off.

Depending on your structure you could use someting like this - again the Temp tables are not the solution the CASE statement is..

CREATE TABLE #temp

(TextValue VARCHAR (5))

INSERT INTO #temp

SELECT 'efg' UNION ALL

SELECT 'abc' UNION ALL

SELECT 'ijk' UNION ALL

SELECT 'Imn' UNION ALL

SELECT 'aaa'

CREATE TABLE #temp1

(TextValue VARCHAR (5))

INSERT INTO #temp1

SELECT 'efg' UNION ALL

SELECT 'abc' UNION ALL

SELECT 'ijk' UNION ALL

SELECT 'Imn' UNION ALL

SELECT 'Baa'

SELECT

CASE WHEN T.TextValue like T1.TextValue THEN 'TRUE' ELSE 'FALSE' END

FROM #temp T

LEFT JOIN #temp1 T1

ON T.TextValue = T1.TextValue

DROP TABLE #temp

DROP TABLE #temp1

Results

-----------------------------

TRUE

FALSE

Koen is correct - without seeing more information it is very difficult to see what you are trying to do and provide a solution..

Andy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

SQL_Surfer (4/4/2012)
I have a comma seperated list of strings like 'abc, efg, ijk, lmn'. ...

In a column of a SQL Server table? Have you tried LIKE()?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 7

If the values are contained in one string separated by commas rather than in separate rows you could try something like this using Jeff's String Splitter

CREATE TABLE #temp

(TextValue VARCHAR (500))

INSERT INTO #temp

SELECT ('efg,abc,ijk,Imn,aaa')

CREATE TABLE #temp1

(TextValue VARCHAR (5))

INSERT INTO #temp1

SELECT 'efg' UNION ALL

SELECT 'abc' UNION ALL

SELECT 'ijk' UNION ALL

SELECT 'Imn' UNION ALL

SELECT 'Baa'

SELECT

item

,T1.TextValue

,CASE WHEN Split.Item LIKE T1.TextValue THEN 'TRUE' ELSE 'FALSE' END

FROM #TEMP

CROSS APPLY dbo.DelimitedSplit8k(TextValue,',') split

LEFT JOIN #Temp1 T1

ON Split.Item = T1.TextValue

DROP TABLE #temp

DROP TABLE #temp1

Results

------------------------------------

itemTextValue(No column name)

efg efg TRUE

abc abc TRUE

ijk ijk TRUE

Imn Imn TRUE

aaa NULL FALSE

Andy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe