Compare strings in SQL SERVER 2000

hi,
We have SQL SERVER 2000 database with a table of customers. We would
like to compare the names of customers for double names. We want to
find same names or names that are look like.
for example:
ID NAME ADDRESS
1 John Smith
100 John Smitth
We consider that it is a very useful we have a ranking for these names.
Have you any idea, how can do this ?.Loop LIKE operator in the BOL
SELECT <columns>FROM Table WHERE col LIKE '%Smith%'
Note: In above case SQL Server will not be able to use an index if you have
one on this column
<akoutoulakis@.gmail.com> wrote in message
news:1131368418.663514.264320@.g47g2000cwa.googlegroups.com...
> hi,
> We have SQL SERVER 2000 database with a table of customers. We would
> like to compare the names of customers for double names. We want to
> find same names or names that are look like.
> for example:
> ID NAME ADDRESS
> 1 John Smith
> 100 John Smitth
> We consider that it is a very useful we have a ranking for these names.
> Have you any idea, how can do this ?.
>|||> Loop LIKE operator in the BOL
Sorry ,should be Lookup LIKE operator in the BOL
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23qjQhv54FHA.3540@.TK2MSFTNGP10.phx.gbl...
> Loop LIKE operator in the BOL
> SELECT <columns>FROM Table WHERE col LIKE '%Smith%'
>
> Note: In above case SQL Server will not be able to use an index if you
> have one on this column
>
>
> <akoutoulakis@.gmail.com> wrote in message
> news:1131368418.663514.264320@.g47g2000cwa.googlegroups.com...
>> hi,
>> We have SQL SERVER 2000 database with a table of customers. We would
>> like to compare the names of customers for double names. We want to
>> find same names or names that are look like.
>> for example:
>> ID NAME ADDRESS
>> 1 John Smith
>> 100 John Smitth
>> We consider that it is a very useful we have a ranking for these names.
>> Have you any idea, how can do this ?.
>|||On 7 Nov 2005 05:00:18 -0800, akoutoulakis@.gmail.com wrote:
>hi,
>We have SQL SERVER 2000 database with a table of customers. We would
>like to compare the names of customers for double names. We want to
>find same names or names that are look like.
>for example:
>ID NAME ADDRESS
> 1 John Smith
> 100 John Smitth
>We consider that it is a very useful we have a ranking for these names.
>Have you any idea, how can do this ?.
Hi akoutoulakis,
Searching for duplicates:
SELECT Name, COUNT(*) AS NumOfDups
FROM YourTable
GROUP BY Name
HAVING COUNT(*) > 1
Showing all info for duplicates:
SELECT a.ID, a.Name, a.Address
FROM YourTable AS a
WHERE EXISTS
(SELECT *
FROM YourTable AS b
WHERE b.Name = a.Name
AND b.ID <> a.ID)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you for a help.