SQL Server Not Returning Results With Specific Words in Parameterised Query

• mharran

  SSC-Addicted

  Points: 419

  More actions

  November 20, 2011 at 3:39 pm

  #252118

  I'm using a Parameterised Query to search for a particular word picked up from a search box but certain words are creating a problem. One of these is the word "pad".

  It's a recipe database I'm using and there is a recipe called "pad Thai" in it; if I pass the word "thai" into the query parameter, that recipe is returned fine. If I pass the word "pad" in, the query runs for about 10 seconds and returns no records.

  I was suspicious of "pad" being a reserved keyword so I've been trying other keywords; the only one I've found causing the same problem so far is "proc".

  Any ideas what the problem is and, more importantly, how I can get around it.?

• 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]