T-SQL Split String Like Clause

Question

I have declare @a varchar(100) = 'abc bcd cde def'. What I need is to select from a table where a column is like 'abc' or 'bcd' or 'cde' or 'def'. I can use a split function and a while to get what I want, but somewhere I saw a smart solution using replace or something similar and I just can't remember it.

I know I can use an xml variable, and parse it that way. However, the value is part of a large procedure, and the best way for me is to use it in string form.

I know I can solve this by building a dynamic sql query, but that is not an option in the domain I'm working in.

Are you looking for http://stackoverflow.com/questions/2647/split-string-in-sql or are you have trouble doing an `IN` on the results of the split? — Conrad Frix, Nov 08 '11 at 19:36
There are tons of solutions, xml datatype, dinamyc queries, Im just looking for the one I need. — , Nov 08 '11 at 19:36
You're looking for something you forgot and can't really describe. Good luck :) — MatBailie, Nov 08 '11 at 19:39
also see http://stackoverflow.com/questions/697519/split-function-equivalent-in-tsql — dotjoe, Nov 08 '11 at 19:56

score 1 · Accepted Answer · answered Nov 08 '11 at 20:25

Damn, I just can remember the solution. Its a hack, a little dirty trick that do the job.

Anyways, I ll use the code bellow (Im over SQL Server 2008), is it a good idea? I prefer it over the dirty split. Is it more performatic?

declare @w varchar(100) = 'some word'
declare @f xml

set @f = '<word>' + replace(@w, ' ', '</word><word>') + '</word>'

select
    template.item.value('.', 'varchar(100)') as word
from @f.nodes('/word') template(item)

Test it and see. If you get results you don't understand, then ask another question. — MatBailie, Nov 08 '11 at 21:58

score 0 · Answer 2 · edited May 23 '17 at 12:21

0

In tsql you can use a pattern col like '[abcd]'

http://msdn.microsoft.com/en-us/library/ms179859.aspx

For matching multiple words (not letter) and without dynamic SQL, you'll have to get the values into a temp table. For a split function try this page http://www.sommarskog.se/arrays-in-sql-2005.html#iterative and look at the List of Strings function iter_charlist_to_table.

Or maybe you are thinking of this little trick Parameterize an SQL IN clause from the SO CEO.

edited May 23 '17 at 12:21

Community

1
1

answered Nov 08 '11 at 19:36

dotjoe

26,242
5
63
77

Note: This matches individual characters. If you wanted to match words, this wouldn't work. – MatBailie Nov 08 '11 at 19:38
I think you nailed it. Let me take a deep look. Thankx. What do you think about my dirty xml based technique? – Nov 08 '11 at 20:31
personally I'd do the split-to-table function, but I never really used xml variable in sql server so I can't say which is faster. – dotjoe Nov 08 '11 at 20:50

score 0 · Answer 3 · answered Nov 08 '11 at 19:43

Use a function to split the individual items into a table, one record per item. Then you simply join to that table.

insert into #FilterTable (filters)
select Items from dbo.Split(@YourFilterString)

select * 
  from YourTable yt
  join #FilterTable f on f.filters = yt.YourColumn

Of course my example is using equality. It gets more complicated if you truly intend to use "like" with wildcards.

score 0 · Answer 4 · answered Nov 08 '11 at 20:21

0

for 4 sections max

WHERE
    PARSENAME(REPLACE(@a, ' ', '.'), 1) = 'xxx'
    OR
    PARSENAME(REPLACE(@a, ' ', '.'), 2) = 'xxx'
    OR
    PARSENAME(REPLACE(@a, ' ', '.'), 3) = 'xxx'
    OR
    PARSENAME(REPLACE(@a, ' ', '.'), 4) = 'xxx'

answered Nov 08 '11 at 20:21

gbn

422,506
82
585
676

T-SQL Split String Like Clause

4 Answers4