CODEWITHSUNDEEP
c#sqlsql-server
Mike Hughes
Mike Hughes

Reputation: 77

String of text search

I have the following string.

car, bus, bike, house

I would like to split these into individual strings and use them in a where clause so it would like this:

SELECT [text]
FROM [table]
WHERE
text LIKE 'bus'
or text LIKE 'car'
or text LIKE 'bike'
or text LIKE 'house'

How would you go through the original string and split them out into individual substrings. The original text string is being passed as variable from a GUI. So far we have this

REPLACE(LEFT('%BIKE, BUS, HOUSE%',CHARINDEX(',','%BIKE, BUS, HOUSE%')),',','%'),
REPLACE(SUBSTRING('%BIKE, BUS, HOUSE%',CHARINDEX(',','%LADDER, BA%'),LEN('%BIKE, BUS, HOUSE%')),',','%'),

But that only brings back a substring before the first comma and keeps everything after. eg 'bike' and 'car, bus, house'.

Any ideas?

Upvotes: 0

Views: 77

Answers (2)

Hart CO
Hart CO

Reputation: 34784

You can use XML functionality to turn your string into an array, then simply JOIN with wildcards:

DECLARE @string VARCHAR(100) = 'car, bus, bike, house'
;WITH cte AS (SELECT RTRIM(LTRIM(Split.a.value('.', 'VARCHAR(100)'))) AS Txt    
              FROM  (SELECT CAST ('<M>' + REPLACE(@string, ',', '</M><M>') + '</M>' AS XML) AS DATA
              ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a))
SELECT DISTINCT [text]
FROM [table] a
JOIN cte b
  ON a.[text] LIKE '%'+b.Txt+'%'

Ideally you'd pass your array into a table variable and just have to run the JOIN at the bottom.

Upvotes: 1

James Curran
James Curran

Reputation: 103575

Use LINQ with the PredicateBuilder (http://www.albahari.com/nutshell/predicatebuilder.aspx)

(Sample -- I had a table of ZipCodes handy)

var codes = "77,88,99".Split(',');

var predicate = PredicateBuilder.False<ZipCodes>();
foreach(var c in codes)
    predicate = predicate.Or(z=>z.ZipCode.Contains(c));

var answer = this.ZipCodes.Where(predicate).ToList();

Upvotes: 1

Related Questions