Search for any part of text from list

• Deepak.Sharma507

  Ten Centuries

  Points: 1225

  More actions

  October 12, 2011 at 6:40 am

  #390599

  Hello all,

  I an kind of confused as to how to put my query. Let me try.

  I have a list of names (James wolf, Charlie greenway, Tim holloway, Sarah jones, Molly glenn etc etc.)

  What I need is to search in the field (Customer_Name) of the table any part of the list.

  Like it should give me result if even a part of name matched with the Customer_Name

  Customer_Name

  ----------------

  Wolf James

  Charlie G

  Tim Holoway

  Sarah

  Molly G

  So if I run the query it should find all the records from table Customer_Name as he part of name from list is matching the part of value in Customer_Name.

  I hope I made myself clear. Please do write if you need more clarification. Which I think you will need 😉

  Any suggestion or hint will really help.

  Thanks,

  Deepak

• Lowell

  SSC Guru

  Points: 323518

  More actions

  October 12, 2011 at 6:59 am

  #1393399

  part of a name...no, i don't think so.

  you could split the list of names, and do a LIKE or charindex to see if whole words exist, by splitting on the space:

  this returns a ton of results because of the letter "G" int he split, but would match "tim" OR "wolf" Or "james", for example.

  this assumes you have the split function dbo.DelimitedSplit8K installed.

  with myMatchData (SearchTerms)

  AS

  (

  SELECT 'Wolf James' UNION ALL

  SELECT 'Charlie G' UNION ALL

  SELECT 'Tim Holoway' UNION ALL

  SELECT 'Sarah' UNION ALL

  SELECT 'Molly G'

  )

  select *

  from sys.objects TB

  CROSS JOIN myMatchData --the table reprenting your real table

  CROSS APPLY dbo.DelimitedSplit8K(myMatchData.SearchTerms,' ') myfn

  where CHARINDEX(myfn.Item,TB.name) > 0

  -- and LEN(myfn.Item) > 1

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Rob Schripsema

  SSCertifiable

  Points: 7469

  More actions

  October 12, 2011 at 12:07 pm

  #1393640

  Deepak -- you could achieve much of this by using SQL Server's Full Text search capabilities. There's a bit of overhead, but it's much, much faster than a LIKE search and is much simpler to use.

  It won't do things like return 'Wolf', 'Molly' or 'Holoway' if you query with just the letter 'o', but it WILL allow you to do prefix searches, so 'Mo*' will return Molly's record just like a LIKE query -- and much faster.

  There's a lot of info in BOL about Full Text indexing/searching, if you're interested.

  Rob Schripsema
  Propack, Inc.