T-SQL Pattern matching

Question

I am trying to find a way to query rows of data by using a "multivalue" pipe delimited column in another table as a WHERE clause. SQL SERVER 2005

This is my best description of the problem:

Imagine a pipe delimited column set to a variable like @LIST = 'Bob|Mary|Joe'

then I am trying to find a match like this

Select * from Users where FirstName = 'Joe'

but extended to be

Select * from Users where FirstName "IS CONTAINED IN" @List

which would return all the Bob, Mary and Joe entries. Thanks for your help.

Answer 1

Check out the PATINDEX() function. It's a bit limited, but it should do what you're looking for.

Answer 2

You can use a split udf and join it to your main query. see this link for the code and an example. Your query would end up looking like this. This is untested but hopefully this points you in the right direction.

Select A.* from Users A JOIN dbo.Fn_Split(@ValueArrayString, '|') B on A.FirstName = B.value

Answer 3

See udf___Txt_SplitTab from Andrew Novick

Select * from Users where FirstName IN (Select Item From udf_Txt_SplitTAB (@LIST, '|'))

Answer 4

How about

Select * from Users where CHARINDEX(FirstName + '|', @List + '|') > 0

A little annoying that you have to append the pipe delimiter to both strings, but it works, and it's probably faster than a function.

Answer 5

I like MrTelly's solution. However, it's only taking care of half the false-positives. The full solution is as follows:

Select * from Users where CHARINDEX('|' + FirstName + '|', '|' + @List + '|') > 0

The pipe needs to be added on both ends