June 24, 2008 at 10:20 am
I have a CRM database where I would like to allow the user to enter 'A%' and have the search query return all contact names that begin with A and/or Ā, Ā, Ă, ă, Ą, ą, à, á, å, ä, ã, â.
Anybody know of a way to do this.
Nigel West
UK
June 24, 2008 at 11:29 am
...?
Use the SOUNDEX or DIFFERENCE functions?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 24, 2008 at 12:24 pm
You'll have to test it if suites your purpose because it is language dependant !
declare @tmptest table (idnr int not null identity(1,1) primary key, LName varchar(50) not null )
insert into @tmptest (LName)
select lname
from (
select 'bijnens' as LName, soundex('bijnens') as SoundexValue
union all
select 'bainens', soundex('baainens')
union all
select 'beinens',soundex('beinens')
union all
select 'beijnens', soundex('beijnens')
union all
select 'bynens', soundex('bynens')
union all
select 'bijnans', soundex('bijnans')
union all
select 'bijkans', soundex('bijkans')
union all
select 'janssen', soundex('janssen')
union all
select 'janssens', soundex('janssens')
union all
select 'jansen', soundex('jansen')
union all
select 'jans', soundex('jans')
union all
select 'sjans', soundex('sjans')
union all
select 'chans', soundex('chans')
) A
Select T1.IdNr, T1.LName , T2.LName
from @tmptest T1
left join @tmptest T2
on T1.IdNr <> T2.IdNr
and soundex(T1.Lname) = soundex(T2.Lname)
order by T1.IdNr
--- 2
Select T1.IdNr, T1.LName , T2.LName
from @tmptest T1
left join @tmptest T2
on T1.IdNr <> T2.IdNr
and soundex(T1.Lname) = soundex(T2.Lname)
where DIFFERENCE(T1.LName, T2.LName) = 4
order by T1.IdNr
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 25, 2008 at 2:23 am
Thanks for the advice, at first sight it appears as though the combination of SOUNDEX and DIFFERENCE might give me the answer I'm looking for, however, having run a few tests it appears that the DIFFERENCE function in particular is way short of the mark.
For example:
SELECT DIFFERENCE('seguin','Sasayama')
Returns a value of 4, meaning that these two words are very similar.
Go figure....... the only similarity is that they start with the same letter.
Nigel West
UK
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply