String of text search

Question

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?

Is it an option to do this in a programming language? (PHP, Python, etc.) — mopo922, Jan 14 '15 at 16:26
Do you actually want `LIKE 'bus'` or do you really want `LIKE '%bus%'`? — Hart CO, Jan 14 '15 at 16:27
This is trivial in linq and there are dozens of answers out there on how to do it. — crthompson, Jan 14 '15 at 16:27
you should have a look at CONTAINS. http://msdn.microsoft.com/en-us/library/ms187787.aspx — Donal, Jan 14 '15 at 16:28
How about NOT passing a delimited string? Pass the parameters individually, or [as an array](http://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure). — crthompson, Jan 14 '15 at 16:30
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm — Tab Alleman, Jan 14 '15 at 16:36
possible duplicate of [How do I split a string so I can access item x](http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x) — hatchet - done with SOverflow, Jan 14 '15 at 16:40
If you do go the split route, you should change your sql to compare against `','+[text]+','`, and then search for things like `'%,bike,%'`, otherwise, searching for `"%bike%"` will return "motorbike" as a match. — hatchet - done with SOverflow, Jan 14 '15 at 16:49
@hatchet If the `[text]` field is also a delimited list then yes, otherwise that's not necessary. — Hart CO, Jan 14 '15 at 16:56
Search for "convert CSV to table SQL server" and look at some of the results that come up. You can do a regular join on the table then. — theMayer, Jan 14 '15 at 18:38

score 1 · Accepted Answer · answered Jan 14 '15 at 16:38

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();

Hart CO · Answer 2 · 2015-01-14T17:07:07.783

1

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.

edited Jan 14 '15 at 17:07

answered Jan 14 '15 at 16:49

Hart CO

31,791
5
41
56

I do a ton of ETL work and I use this function daily. – Mark Kram Jan 14 '15 at 17:08

String of text search

2 Answers2