Simple PATINDEX and Escape codes

• BillyWilly

  SSCrazy

  Points: 2304

  More actions

  September 8, 2004 at 8:55 am

  #60189

  HI

  Trying to search for a particular set of characters within a string.

  The problems is, some of the characters I'm looking for appear to be escape codes in SQL Server.

  Essentially I want to look for ANYTHING that may be a delimeter.

  Something like -,|`~ etcetera...

  So, this:

  SELECT PATINDEX('%[`~!@#$&*()+={}|\:;%"<>,.]%', 'A.A')

  Returns 2 which is correct.

  I've found the position of the period(.) character.

  However, if I start adding any of the following characters - the results start varying since they are escape codes.

  %^-_[]'

  How can I also add the listed characters (%^-_[]') to my list or characters to search for ?

  Thanks in advance - B

• hoo-t

  SSChampion

  Points: 12713

  More actions

  September 8, 2004 at 10:15 am

  #522152

  Double them up. ie. the percent sign '%' is a wildcard to sql. To look for a percent sign specifically you would place it in the literal twice - patindex('%%')

  Steve

• BillyWilly

  SSCrazy

  Points: 2304

  More actions

  September 8, 2004 at 10:19 am

  #522153

  Thanks Steve.

  In the meantime I'm simply doing the negative - or looking for character NOT in the list I'm looking for.

   

  This: SELECT PATINDEX('%[^a-z0-9]%', 'AbC12|3ABC')

  Returns 6 which is correct for me.

  I'm only looking for alphas and numbers as being valid.

  All others are considered delimeters.

  Thanks again - B

• Adam Machanic

  SSCoach

  Points: 15370

  More actions

  September 8, 2004 at 10:27 am

  #522159

  Watch those ranges....

  create table #search(somechar char(1))

  go

  declare @i int

  set @i = 1

  while @i < = 256

  begin

  insert #search

  select char(@i)

  where char(@i) not like '[abcdefghijklmnopqrstuvwxyz0123456789]'

  set @i = @i + 1

  end

  go

  SELECT *

  from #search

  where PATINDEX('%[^a-z0-9]%', somechar) = 0

  go

  --
  Adam Machanic
  whoisactive