Parameter Problem

• THIRUMALARAJKUMAR

  SSC-Addicted

  Points: 488

  More actions

  January 24, 2006 at 5:06 am

  #364777

  Hi,

  I have a query which is returning the values based on parameter.

  If i give the following query it is returning the values successfully.

  select * from externalBrands where extbName like '%^[7 Diamonds !!! !^]%' Escape '^'

  but if i give the query like this, it is returning zero rows.

  declare @str varchar(50)

  set @str =  '''%^[7 Diamonds !!! !^]%'' Escape ''^'''

  print @str

  select * from externalBrands where extbName like @str

  When i check the @str, the value of @str is '%^[7 Diamonds !!! !^]%' Escape '^'

  So, when i pass the string directly to the query, i am getting rows, but if i pass thru variable, i am getting the zero rows.

  Please let me know the actual reason?

  Thanks in advance.

  Regards

  Thirumalaraj

   

   

   

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  January 24, 2006 at 7:45 am

  #616665

  The ESCAPE cannot be within the string passed to LIKE it must be outside e.g.

  set @str =  '%^[7 Diamonds !!! !^]%'

  where extbName like @str Escape '^'

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