SQL Server Not Returning Results With Specific Words in Parameterised Query

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  November 20, 2011 at 5:24 pm

  #1410447

  I do not think it is the word "pad" that is the problem, using SQL 2008 R2

  CREATE TABLE #T(Id INT IDENTITY(1,1),Title VARCHAR(30))

  INSERT INTO #T(Title)

  SELECT 'pad Thai' UNION ALL

  SELECT 'Thai pad' UNION ALL

  SELECT 'paddle' UNION ALL

  SELECT 'poodle' UNION ALL

  SELECT 'Onion soup'

  SELECT Id,Title FROM #T WHERE Title LIKE '%pad%'

  SELECT Id,Title FROM #T WHERE Title LIKE '%Thai%'

  DECLARE @Find VARCHAR(30)

  SET @Find = 'pad'

  SELECT Id,Title FROM #T WHERE Title LIKE '%'+@Find + '%'

  Result:

  IdTitle

  1pad Thai

  2Thai pad

  3paddle

  Now how does that column (in my example Title) get its data?

  Is it imported from another source such as an EXCEL work sheet, Word document, passed from a main frame computer and imported?

  It is possible that the Title field includes, in some rows, none visible characters such as:

  Hex Value Character

  1 Start of heading

  2 Start of text

  3 End of text

  so on and so forth - for a list of additional such characters refer to

  http://www.lookuptables.com

  To locate such characters use either CHARINDEX or PATINDEX

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]