Where clauses that have ''['' character in value.

• geoff hopkins

  Ten Centuries

  Points: 1147

  More actions

  March 13, 2006 at 2:39 am

  #168014

  I am building up some dynamic t-sql to produce a recordset where one of the parameters sets the where clause.

  The issue is that that data contains '[' and ']' brackets

  so if the user sets the parameter for the report that includes these brackets no results are returned even though there are values.

   

• RyanRandall

  SSChampion

  Points: 13623

  More actions

  March 13, 2006 at 4:51 am

  #626180

  I'm guessing you're using the LIKE clause and need to use ESCAPE...

  DECLARE @t TABLE (v VARCHAR(100))

  INSERT INTO @t

        SELECT 'abc'

  UNION SELECT 'a[c'

  SELECT * FROM @t WHERE v LIKE '%[%' --0 rows

  SELECT * FROM @t WHERE v LIKE '%\[%' ESCAPE '\' --1 row

  Ryan Randall

  Solutions are easy. Understanding the problem, now, that's the hard part.