Sql server, reg-ex


I have a stored proc, that contains a variable  @szQuickValue
The contents of this variable can sometimes be street addresses:
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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You can use FULL-TEXT SEARCH ability. See how to query with Full Text-Search.
nachtmskAuthor Commented:
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.
 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.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

nachtmskAuthor Commented:
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ãoMSSQL Senior EngineerCommented:
I think you didn't check the link I sent. They are full of examples.
nachtmskAuthor Commented:
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ãoMSSQL Senior EngineerCommented:
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.

nachtmskAuthor Commented:
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ãoMSSQL Senior EngineerCommented:
nachtmsk, did you solve your issue?
I would also go with Full-Text Searching, or even just comparisons based on a REPLACE(column, 'Street', 'St') and the LIKE operator and so on.

That said, there is no support for Regular Expressions in SQL. To do that, you have to create a  SQL CLR library using a .NET language like VB.NET or C#. This assembly can then be installed into the SQL Server and you can then use the .NET Regular Expression libraries that way. This article gives you guidelines on doing just that... https://www.simple-talk.com/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-example/ . I must point out though, that I do not think this is necessarily easier than the earlier options and will still need you to make adjustments to your complex SQL proc.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.