name search idea requested

Question

name search idea requested

mike 57299

SSCrazy

Points: 2529
More actions
February 18, 2015 at 2:21 pm

#294787

Hi all.
I have a Customer table with a name field of char(50). I want to do a search.
create table #test (customer_id int, cust_name char(50));
insert into #test values (1,'Sunday Markets'),
(2, 'Monday Markets'),
(3, 'Going Away'),
(4, 'My Market'),
(5, 'Away on Monday')
I would like to find any record where at least 2 words match in each of the following phrases:
"going to the market on sunday" - (1, 3)
"monday's away" - (none)
"Go away from Monday" - (5)
So I am looking to match 2 words of any phrase from within the Cust_name field. Any ideas?
Thanks,
Mike

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

this is the whole idea of what full text searching is all about. you leverage the ability to full text search specific columns,a nd search agaisnt that specialized index:

there's pretty good but slightly dated article here i actually implemented years ago:

A Google-like Full Text Search[/url]

and a whole lot of additional articles and info specifically about full text:

http://www.sqlservercentral.com/search/?q=full+text+search&t=a&sort=relevance

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!

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

If full text search is not a possibility for you (but hopefully it will), you could try an alternative by splitting the names/phrases into separate words to validate that at least 2 words match.

Check the DelimitedSplit8K in here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

create table #test (customer_id int, cust_name char(50));

insert into #test values (1,'Sunday Markets'),

(2, 'Monday Markets'),

(3, 'Going Away'),

(4, 'My Market'),

(5, 'Away on Monday')

CREATE TABLE #Phrases(

phrase varchar(50))

INSERT INTO #Phrases VALUES

('going away to the markets sunday'),

('monday''s away'),

('Go away from Monday' )

--Single phrase option

DECLARE @Phrase varchar(50) = 'Go away from Monday'

SELECT customer_id, cust_name, COUNT(*)

FROM #test

CROSS APPLY dbo.DelimitedSplit8K( cust_name, ' ') n

CROSS APPLY dbo.DelimitedSplit8K( @Phrase, ' ') p

WHERE n.Item = p.Item

GROUP BY customer_id, cust_name

HAVING COUNT(*) >= 2

--A table for multiple phrases

SELECT customer_id, cust_name, phrase, COUNT(*)

FROM #test

CROSS JOIN #Phrases

CROSS APPLY dbo.DelimitedSplit8K( cust_name, ' ') n

CROSS APPLY dbo.DelimitedSplit8K( phrase, ' ') p

WHERE n.Item = p.Item

GROUP BY customer_id, cust_name, phrase

HAVING COUNT(*) >= 2

GO

DROP table #test

DROP TABLE #Phrases

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mike 57299 SSCrazy Points: 2529 More actions · Answer 3

mike 57299

SSCrazy

Points: 2529

February 18, 2015 at 3:44 pm

#1778501

This solution will work great!

Thank you.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

A side point. Char(50) is probably not the optimal datatype to use here, as your names have varying lengths. I would recommend that you switch to Varchar(50) to save space.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel