query weirdness (locating [ character)

• ckempste

  SSCoach

  Points: 17983

  More actions

  April 2, 2002 at 12:28 am

  #78288

  Hi all

  Any ideas on this one:

  example record:

  ENGINEERING TRADESPERSON (FABRICATION) [Vehicle Bodybuilding]

  query:

  select * from training_product where registered_name like '%[%'

  results:

  <no rows returned>

  query:

  select * from training_product where registered_name like '%]%'

  results:

  ENGINEERING TRADESPERSON (FABRICATION) [Vehicle Bodybuilding]

  ??Ideas??

  
  Chris Kempster
  www.chriskempster.com
  Author of "SQL Server Backup, Recovery & Troubleshooting"
  Author of "SQL Server 2k for the Oracle DBA"

• Andy Warren

  SSC Guru

  Points: 119902

  More actions

  April 2, 2002 at 5:13 am

  #426423

  [ is an escape character. To use it, you'd do LIKE '[ [ ]'. BOL has a list of them. Strangely the left bracket requires special handling while the right - as you discovered - does not!

  Andy

  Andy
  Connect with me on LinkedIn

• Antares686

  SSC Guru

  Points: 125444

  More actions

  April 2, 2002 at 5:26 am

  #426425

  Yeah, like uses [ as the begining delimiter for a list of character replacements.

  Example.

  If you wanted to find a list of items that started with A, C, D, or G then

  SELECT * FROM tblX WHERE colX LIKE '[A,C,D,G]%'

  Now since there was no [ in the ] version of your like it did not recognize as an ending delimeter. However to get around this if you do.

  SELECT ...... LIKE '%[[]%' it will understand that you are looking for a value with [ in it.

  "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

• ckempste

  SSCoach

  Points: 17983

  More actions

  April 2, 2002 at 3:57 pm

  #426493

  Very interesting, thanks for the help. Ill give it a go later today.

  Cheers

  Ck

  
  Chris Kempster
  www.chriskempster.com
  Author of "SQL Server Backup, Recovery & Troubleshooting"
  Author of "SQL Server 2k for the Oracle DBA"