Regular Expression Help

• knovak

  Right there with Babe

  Points: 779

  More actions

  September 10, 2009 at 11:27 am

  #138001

  I am writing a query to remove any special characters that are not A-Z or 0-9 or comma, or space with the following query below...

  select *

  from ClientInfo

  where ClientName like '%[^ ^a-z^A-Z^0-9^,]%'

  Test data is...

  TEST1 1. 0001

  TEST2, 0002

  TEST3! 0003

  TEST4^ 0004

  TEST5^ 0005

  My result set right now is...

  TEST1 1. 0001

  TEST3! 0003

  Which is almost there, but I consider the "^" to be a special character so there should be 2 more records in the result set.

  How do I setup the regex so that a caret is considered a special character?

  Thanks

  Kyle

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  September 10, 2009 at 12:16 pm

  #1051868

  Look up the LIKE operator in Books On Line and pay particular attention to the use of the ESCAPE modifier.

  ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/581fb289-29f9-412b-869c-18d33a9e93d5.htm

  CREATE TABLE #Td(

  ClientInfo VARCHAR(50)

  )

  INSERT INTO #td(ClientInfo)

  SELECT 'TEST1 1, 0001' UNION ALL

  SELECT 'TEST2,0002' UNION ALL

  SELECT 'TEST3!,0003' UNION ALL

  SELECT 'TEST4^,0004' UNION ALL

  SELECT 'TEST5^, 0005'

  select *

  from #Td

  where ClientInfo like '%[#^ ^a-z^A-Z^0-9^,]%' ESCAPE '#';

  DROP TABLE #td

  Returned:

  TEST1 1, 0001

  TEST2,0002

  TEST3!,0003

  TEST4^,0004

  TEST5^, 0005

  Note: Since your original statement did not handle the comma in the field I did not modifiy it to do that - leaving that to you to handle.

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]