How do I search for "'" character

• dusang

  SSC Enthusiast

  Points: 164

  More actions

  April 29, 2003 at 3:04 pm

  #158881

  Hi,

  I've got table Product with column Description. Description can contain character "'". I use sp to seach for products as follows:

  create procedure SearchProduct

  (

  @Desc varchar(50)

  )

  AS

  SELECT * FROM Product

  WHERE Description LIKE '%@Desc%'

  GO

  If @Desc contais "'" character (e.g. "John's") sp fails.

  How do I fix this?

  Thanks.

  dusan

• 5409045121009-7368

  SSCoach

  Points: 15740

  More actions

  April 29, 2003 at 3:13 pm

  #455733

  Try:

  SELECT * FROM Product

  WHERE Description LIKE '%'+@Desc+'%'

  Example:

  Declare @d varchar(8000),

  @arg varchar(10)

  Select @d='ABC''DEF',

  @arg='C''D'

  Select @d

  Select Case When @d like '%'+@Arg+'%' then 'Yes' Else 'No ' end

• shureshs

  Grasshopper

  Points: 14

  More actions

  April 29, 2003 at 5:41 pm

  #455742

  Hi,

  The following code may solve your problem :

  CREATE PROCEDURE SearchProduct

  ( @Desc VARCHAR(50) )

  AS

  BEGIN

  SELECT @Desc = Replace(@Desc, '''', '''''')

  SELECT * FROM Product

  WHERE Description LIKE '%' + @Desc + '%'

  END

  GO