how to find this pattern in sql server?

Question

How to find pattern like this in sql server in a table.

I've table with rows like this.

ST CLAIR, TERESA
JR VISH, DALVI
JACK RIDER
JR JK, SMITH

I want to find the rows which follows this pattern ST CLAIR, TERESA so it will return 3 rows like below.

ST CLAIR, TERESA
JR VISH, DALVI
JR JK, SMITH

Query

select * from table
where column like ?

What pattern...? Please describe it more. Two words, comma, another word? — chx, Jul 09 '12 at 04:18
possible duplicate of [microsoft sql equivalent of mysql REGEXP](http://stackoverflow.com/questions/2515259/microsoft-sql-equivalent-of-mysql-regexp) — chx, Jul 09 '12 at 04:23
Hard to say without clarification on what you want. `% %, %` will match `100 200, 300`. To match *words* you need regular expressions. — chx, Jul 09 '12 at 07:43

score 1 · Answer 1 · answered Jul 09 '12 at 04:34

1

Maybe you're looking for a pattern that includes a space character: [column] LIKE '% %, %'

Or if the comma is all you care about just use LIKE '%,%'

answered Jul 09 '12 at 04:34

shawnt00

16,443
3
17
22

LIKE '% %, %' will work - % % to grab the space and , % for comma and suffix. – user1166147 Jul 09 '12 at 04:40

Luxspes · Answer 2 · 2012-07-09T04:44:42.783

You mean this?

select * from table where column like '% %, %'

You can test it like this:

WITH Foo (datacol)
AS( SELECT *
    FROM (  VALUES  ('ST CLAIR, TERESA'),
                    ('JR VISH, DALVI'),
                    ('JACK RIDER'), 
                    ('JR JK, SMITH') 
                    ) AS F (datacol))
SELECT datacol
FROM Foo where datacol like '% %, %';

It will give you the result you are looking for:

 ST CLAIR, TERESA
 JR VISH, DALVI
 JR JK, SMITH

score 0 · Answer 3 · answered Jul 09 '12 at 05:09

As suggested by Luxspes you can use '% %, %' to filter the data or you can use parsename to get the result

Declare @Sample table
(word varchar(255))

Insert into @Sample
values 
('ST CLAIR , TERESA'),('JR VISH, DALVI'),('JACK RIDER'),('JR JK, SMITH')

Select word from @Sample
where parsename(replace(word,',','.'),2) is not null

how to find this pattern in sql server?

3 Answers3