help with the operator LIKE

Question

Hello Guys,

I'm trying to write a stored procedure that should return me all the data that matches certain patterns. For example I would like to pass  the following string as parameter :
'A,B,C,D'

and the stored procedure should return all the rows that match that the name field starts with either A,B,C or D.

Thank you for any help

-Amokachi

Answer 1

The easiest way to do this is like this:

SELECT *
FROM MyTable
WHERE MyName LIKE '[A-D]%'

Just pass the string "[A-D]" as parameter.    If you wish to pass the parameter as separate strings (i.e. "A", B", "C", etc), or some sort of delimited string, "A, B, C, D", it gets a bit more complex.

---

Michael Asher

Answer 2

It's kind of a mouthful, but this should work for you (SQL2005 and beyond only):

declare @startswithletters varchar(100)
set @startswithletters='A,B,C,D'

select *
from MyTable
cross join (select StartsWith=xmlNode.value('.','varchar(10)')
            from (select xmlInfo=convert(xml,'<i>'+replace(@startswithletters,',','</i><i>')+'</i>')) X
            cross apply xmlInfo.nodes('i') as N (xmlNode)) StartsWithList
where FieldName like StartsWith+'%'

Note that this will work with any list of "StartsWith" text.  For example, you could pass 'Abe,Bill,Ch'  and it would return all rows where FieldName starts with 'Abe' or 'Bill' or 'Ch'.

---

--Brad

Answer 3

Thanks for your reply masher2. Actually the parameter doesn't contain single letter elements. It 's something more like this:

"London,Paris, New York".

Sorry, I should have been more specific.

Thanks for your help.

-Amokachi

Answer 4

Then you need a T-SQL string tokenizer.  There's a few dozen ways to do this, here's a couple examples:

http://www.codeproject.com/KB/database/SQL_UDF_to_Parse_a_String.aspx
http://codebetter.com/blogs/raymond.lewallen/archive/2005/10/26/133774.aspx

Take the parsed output, paste a '%' on the end of each string then feed it to your LIKE query.

EDIT: or use Brad's CROSS APPLY approach, which I somehow overlooked before I posted.

---

Michael Asher

Answer 5

Thanks for your reply Brad. I'll  give it a try.

-Amokachi

Answer 6

Read Erland's article for different methods to handle this:
http://www.sommarskog.se/arrays-in-sql.html

--
Plamen Ratchev
http://www.SQLStudio.com

Answer 7

You can also use a cte with the inline cross apply, as shown below.  The functionality is the same, i just find it a little easier to read.

;WITH cte
AS
(
SELECT 
	CONVERT(XML,'<i>' + REPLACE(@startswithletters, '/', '</i><i>') + '</i>') AS CNames
)
SELECT 
	t.id,
	t.col
FROM cte
CROSS APPLY	CNames.nodes('//i') AS x(i)
INNER JOIN @t t
ON
	t.col LIKE x.i.value('.', 'CHAR(1)') + '%'

---

http://jahaines.blogspot.com/

Answer 8

If the delimiter is a comma:

SELECT *
FROM MyTable
WHERE ',' + parameter + ',' LIKE '%,' + MyName + ',%'