Text search for similar patterns

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

Transact-SQL (2000)

Text search for similar patterns

Author

Topic

Taurkon
Starting Member

26 Posts

Posted - 2007-11-20 : 11:51:18

I am in need of some expert advice here so I appreciate any help and thank you in advance.

I am attemptinig to find record in one table that are close to records in another table.

The source for the data is different for each table, and therefore there may be suttle differences due to data entry errors etc...

For example, I am attempting to locate similar addresses...

Source StreetName = "Goldenrod Lane"

Target Data

Charleswood cove
Goldenrod Dr *
Oldrod Rd *
1009th St
Goldenrod Lane *
Charleswood cove
Gold Coast St *

Records with an astrix represent the data I would wish to have returned.

I have reviewed the SQL Server full text searching functionality and it does not seem it will help me out here. Does anyone have any suggestions or any algorithms they can point me to?

Thanks,
Scott

georgev
Posting Yak Master

122 Posts

Posted - 2007-11-20 : 12:51:28

You want to use what is called a LIKE comparison with the use of wildcard characters such as percentage symbols (%).
[CODE]
SELECT street
FROM addresses
WHERE street LIKE 'Gold%'
[/CODE]
I can't think of a way to get exactly what you want :D

George
<3Engaged!

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2007-11-20 : 20:38:41

quote:
Originally posted by Taurkon

I am in need of some expert advice here so I appreciate any help and thank you in advance.

I am attemptinig to find record in one table that are close to records in another table.

The source for the data is different for each table, and therefore there may be suttle differences due to data entry errors etc...

For example, I am attempting to locate similar addresses...

Source StreetName = "Goldenrod Lane"

Target Data

Charleswood cove
Goldenrod Dr *
Oldrod Rd *
1009th St
Goldenrod Lane *
Charleswood cove
Gold Coast St *

Records with an astrix represent the data I would wish to have returned.

I have reviewed the SQL Server full text searching functionality and it does not seem it will help me out here. Does anyone have any suggestions or any algorithms they can point me to?

Thanks,
Scott

We had same problem, but I choose to put this on my application. But anybody could make in SQL would be a great help.

Thanks in advance.

For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-28 : 17:39:50

There are fuzzy algorithms and stuff you can search for. Perhaps, you might be able to use something like SOUNDEX?

SELECT SOUNDEX('Goldenrod  dr'), SOUNDEX('Goldenrod  drive')
SELECT SOUNDEX('Goldenrod  Ln'), SOUNDEX('Goldenrod Lane')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources