asked on

Sql server, reg-ex

Hi,

I have a stored proc, that contains a variable @szQuickValue
The contents of this variable can sometimes be street addresses:
Examples:
123 Smith St.
123 Smith Ct.
Sometimes my users will type in 123 Smith Street or 123 Smith Court and the search returns nothing because the string doesn't match.
I'm trying to think of a good way to broaden my result set.
So, I thought of a RegEx that will allow "St" and "Street" to both be accepted as the same.
Writing RegEx in SQL is foreign to me. I write them in perl often, but I'm not sure how to do this one in Sql.
Any help is much appreciated.

Thanks
Nacht

Vitor Montalvão

You can use FULL-TEXT SEARCH ability. See how to query with Full Text-Search.

nachtmsk

ASKER

Thanks for the comment Victor.
But the code for this proc is already written (not by me) and it's long. Putting in major changes isn't really an option. I'm looking for a quick fix. That's why I thought a RegEx would work.
I have a few strings that need to be recognized as other strings.
Namely
St = Street
Ct = Court
Blvd = Boulevard
Rd = Road

Maybe a few more.
if I have a variable -- say "@address" that contains "123 Stone St". How can I modify the contents of that variable to check to see if "St" in in there and if so, change it to 'Street' without matching the "St" in Stone.

Thanks,
N

Vitor Montalvão

Full-text search is easy to implement and doesn't need big changes in your code. Only filter in the SELECT need to be changed to accept full-text search functions. The same thing that you'll need to change with your RegEx.
But is up to you to chose the solution that you feel more comfortable with.

nachtmsk

ASKER

Thanks for your suggestions but they were rather broad. I was hoping for something more specific with included examples.
But as I said, thanks anyway.

Vitor Montalvão

I think you didn't check the link I sent. They are full of examples.

nachtmsk

ASKER

Yes, it was full of examples.. too many examples actually. I looked through it and couldn't really figure out where my problem fit into the MS documentation. You are obviously an expert in this field. I am not. I appreciated the link but usually when I post on EE, I am looking for and get more help then just a link to a help page - maybe some code examples for my particular issue. I'm not trying to be nasty, just truthful.

Vitor Montalvão

I see but I'm trying to show you how to fish and not giving you the fish. Specially because this is a more complex solution than "write this code or use this function".

Also, doing everything for you isn't my way to be here in EE. You can wait for another Expert to help you.

Cheers

nachtmsk

ASKER

I understand and I recognize that is what you were doing. But in this particular instance, I don't have time to learn how to fish. And I wasn't asking you to do everything for me. But I was asking for more then you did do. I will look elsewhere for help. Thank you for responding to my initial post.

Vitor Montalvão

nachtmsk, did you solve your issue?

ASKER CERTIFIED SOLUTION

Mlanda T

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mlanda T

You can also consider some algorithms such as the Levenshtein Algorithms which tries to find the Edit Distance between strings. It can also help to find matches between "123 Smith Street", "123 Smit St", "123 Smiths Street", "123 Smoth Stret" and so on.

https://www.simple-talk.com/blogs/2015/01/05/string-comparisons-in-sql-edit-distance-and-the-levenshtein-algorithm/
http://www.pawlowski.cz/2010/12/sql_server-fuzzy-strings-matching-using-levenshtein-algorithm-t-sql-vs-clr/