Why my reg matching returns nothing?

  • 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.

     

  • If you read the documentation about LIKE (https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15) you will see that it does not support regex the way you are trying to use it.

     

Viewing 2 posts - 1 through 1 (of 1 total)

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