Like Search - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Like Search

Author

Topic

rme8494
Yak Posting Veteran

98 Posts

Posted - 2005-03-03 : 15:30:34

Is there a better way to do this like search?

WHERE
REPLACE(REPLACE(CustomerName,'.',''),',','') LIKE REPLACE(REPLACE(@SearchString,'.',''),',','')

Basically what I'm trying to do is respond to a user request where we have customers in our database that could be named US Govt, or U.S. Govt. So I was asked to take periods, commas and dashes out of both sides of the search. What I've come up with so far is what I have above. It doesn't look that nice, is there a better way?

Thanks,
Ryan

Ryan Everhart
SBC

SBC. Going Beyond the Call!

trivialusername
Starting Member

6 Posts

Posted - 2005-03-03 : 15:41:58

Have you tried soundex?

where
soundex(customername) = soundex(@searchstring)

This is a really useful text matching function.

Rob

rme8494
Yak Posting Veteran

98 Posts

Posted - 2005-03-03 : 15:56:16

trivial,
That didn't have the result I was looking for...

Example
DECLARE @cn VARCHAR(50)

SET @cn = 'AF ASSOCIATES INC'

SELECT *
FROM TECustomer
WHERE SOUNDEX(CustomerName) LIKE SOUNDEX(@cn)

Should return: A.F. ASSOCIATES INC

Instead it returned:

AVAYA FINANCIAL SERVICES
AUB FAITH COM HOSP INC
ABBA WOMENS CENTER
ABBY EXEC SVCS
AHEPA 29 INC
AHEPA
AHEPA INC.
AHF WINDSOR INC C/O KETTLEBROOK HEALTH CA
AVAYA COMMUNICATION
AVP INC
AVE MARIA PRESS

Ryan Everhart
SBC

SBC. Going Beyond the Call!

trivialusername
Starting Member

6 Posts

Posted - 2005-03-03 : 17:01:47

You're right. Sorry about that. Looks like soundex has issues with multiple word strings. Will store that in the 'ole memory bank for future reference.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources