SQL: Select distinct based on regular expression

Question

Basically, I'm dealing with a horribly set up table that I'd love to rebuild, but am not sure I can at this point.

So, the table is of addresses, and it has a ton of similar entries for the same address. But there are sometimes slight variations in the address (i.e., a room # is tacked on IN THE SAME COLUMN, ugh).

Like this:

id | place_name | place_street

1 | Place Name One | 1001 Mercury Blvd
2 | Place Name Two | 2388 Jupiter Street
3 | Place Name One | 1001 Mercury Blvd, Suite A
4 | Place Name, One | 1001 Mercury Boulevard
5 | Place Nam Two | 2388 Jupiter Street, Rm 101

What I would like to do is in SQL (this is mssql), if possible, is do a query that is like:

SELECT DISTINCT place_name, place_street where [the first 4 letters of the place_name are the same] && [the first 4 characters of the place_street are the same].

to, I guess at this point, get:
Plac | 1001
Plac | 2388

Basically, then I can figure out what are the main addresses I have to break out into another table to normalize this, because the rest are just slight derivations.

I hope that makes sense.

I've done some research and I see people using regular expressions in SQL, but a lot of them seem to be using C scripts or something. Do I have to write regex functions and save them into the SQL Server before executing any regular expressions?

Any direction on whether I can just write them in SQL or if I have another step to go through would be great.

Or on how to approach this problem.

Thanks in advance!

Answer 1

Use the SQL function LEFT:

SELECT DISTINCT LEFT(place_name, 4)

Answer 2

I don't think you need regular expressions to get the results you describe. You just want to trim the columns and group by the results, which will effectively give you distinct values.

SELECT left(place_name, 4), left(place_street, 4), count(*)
FROM AddressTable
GROUP BY left(place_name, 4), left(place_street, 4)

The count(*) column isn't necessary, but it gives you some idea of which values might have the most (possibly) duplicate address rows in common.