Searching for a wildcard character.

• Ben-425648

  SSC Enthusiast

  Points: 101

  More actions

  May 3, 2007 at 1:35 am

  #172234

  Gday,

  I have hit upon a problem that I have never seen before in my years of working with SQL Server, its actually rather embarrasing! Anyway, I need to search for SQL's wildcard characters, ie '_' and '%'. Is there anyway to make them into a literal?

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  May 3, 2007 at 2:18 am

  #704008

  They will always be treated as a literal unless used in wilcard searching with LIKE and PATINDEX in which case you need to 'ESCAPE' them, ie

  LIKE '%[%]%'

  LIKE '%[_]%'

  or

  LIKE '%\%%' ESCAPE '\'

  LIKE '%\_%' ESCAPE '\'

  or

  PATINDEX('%[%]%',column)

  PATINDEX('%[_]%',column)

  Look up "Pattern Matching in Search Conditions" on BOL for more info

  Far away is close at hand in the images of elsewhere.
  Anon.