Why my reg matching returns nothing?

• PasLe Choix

  Say Hey Kid

  Points: 665

  More actions

  February 17, 2020 at 8:34 pm

  #3726568

  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:

  

  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:

  

   

  What am I missing here?

   

  Thank you very much.

   

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  February 17, 2020 at 8:51 pm

  #3726572

  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.