Why my reg matching returns nothing?

Question

Why my reg matching returns nothing?

Say Hey Kid

Points: 665

February 17, 2020 at 8:35 pm

I want to do some match on a dataset that contains location value and the location value is to be determined its country, some data is below:

Rumney, NH, US
Hickory Hills, IL
Patchogue, NY
Wilton, NH
Cedarville University, OH
Shopshire, UK
Dreilinden, Germany
Jarjis, Tunisia
Casca/RS, Brasil

create table location
(
city varchar(50),
country varchar(10)
)

insert into location (city) values ('Rumney, NH, US')
insert into location (city) values ('Hickory Hills, IL')
insert into location (city) values ('Patchogue, NY')
insert into location (city) values ('Wilton, NH')
insert into location (city) values ('Cedarville University, OH')
insert into location (city) values ('Shopshire, UK')
insert into location (city) values ('Dreilinden, Germany')
insert into location (city) values ('Jarjis, Tunisia')
insert into location (city) values ('Casca/RS, Brasil')

I believe Regex is the best way to detect that, '(?!.*UK),\s(\w{2})$' is what I come up:

Screenshot - 2_17_2020 , 3_31_15 PM

Below is my testing:

declare @pattern varchar(20)
set @pattern = '(?!.*UK),\s(\w{2})$'
select @pattern
select * from location where city like '%' + @pattern + '%'

I tested it in SQL 2008 Express, it returns nothing:

Screenshot - 2_17_2020 , 3_33_08 PM

What am I missing here?

Thank you very much.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Please see this thread: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

PasLe Choix Say Hey Kid Points: 665 More actions · Answer 2

Thank you Lynn,

Per your link it says:

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

In my case I used: where city like '%' + @pattern + '%'

set @pattern = '(?!.*UK),\s(\w{2})$'

I also tried not using '%" beside the pattern, I also tried PATINDEX('%' + @pattern + '%', city)

If the above are NOT supported, how can I then use Regex to match?

Can you please enlighten?

Thank you very much.

This reply was modified 4 years, 9 months ago by PasLe Choix.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 3

The answer is simple: you are not in Kansas anymore.

LIKE does not support regular expressions of any flavour. It supports % to mean 0 or more of any characters. It supports _ to mean exactly one of any character. And it supports [] to say exactly one of a set of characters, for instance [abc] or [0-9]. Beware that [a-z] will include A-Z as well, unless you have a binary collation. You can also negate the range: [^0-9].

And that is exactly what you can do, which Lynn has tried to tell you in three posts.

If you want to use real regular expression, you will need to write a CLR function that uses the RegEx classes.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

PasLe Choix Say Hey Kid Points: 665 More actions · Answer 4

Thank you Erland, I see only one post from Lynn, but I did receive three notification emails about her posts.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 5

Well, you started three threads, and Lynn replied to them all...

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 6

PasLe Choix wrote:

Thank you Erland, I see only one post from Lynn, but I did receive three notification emails about her posts.

A common mistake... "Lynn" is a crusty ol' dude that's almost as ugly as I am. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PasLe Choix Say Hey Kid Points: 665 More actions · Answer 7

hmmm, I didn't realize I started three threads, indeed I encountered some hiccup when I posted it - the browser just hangs there - I had to re-submitted it, maybe that's why I "started three threads", my apologies.

PasLe Choix Say Hey Kid Points: 665 More actions · Answer 8

Here is the screenshot of my email inbox. I was wondering why Lynn would have replied three times. Sorry Lynn.

Screenshot - 2_17_2020 , 5_09_49 PM

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

I was trying to keep people from posting on the other threads as well, which is why two of them referenced back to the thread I actually replied on.