How to sperate an address without spces

Question

How to sperate an address without spces

itmasterw 60042

SSCrazy

Points: 2858
More actions
June 17, 2020 at 3:45 pm

#3763924
Hi,
I have address in a database table like this below and should got to -->:
1627W.PINNA --- > 1627 W. PINNA
3000CLARCONAROADLOT252 --> 3000 CLARCONA ROAD LOT 252
2633COURTLANDBLVD --> 2633 COURTLAND BLVD
7113RDAVESOUTH --> 7113 DAVE SOUTH
1123Main Street
If this was one I could do this:
```
Declare @S varchar(20) = '1123Main Street'
Set @s = left(@s,4) + SPACE(2) + right(@s,11)
Select @s
```
to get this --> 1123 Main Street
But how would I do this when each one can need a space in various spots.
Thank you

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

How are we going to know where to put the spaces? Unless you have a full databases of street names for the all the areas you cover, I don't think you can do this programmatically. And even if you do, there are going to be outliers, exceptions, ambiguities and addresses that weren't typed in properly.

John

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 2

Yah, that is what I thought too, but I wanted to see if maybe I was missing something.

Thank you

ScottPletcher SSC Guru Points: 101097 More actions · Answer 3

You need to call the Google address parser/"splitter" function or use some other address parser that does this for you. You will never be able to do this accurately by yourself.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 4

Here is something that could be a start, if you have no other option:

DECLARE @Words TABLE (Words nvarchar(20));
INSERT INTO @Words 
VALUES ('ROAD'),
       ('BLVD'),
       ('Street'),
       ('SOUTH')

DECLARE @Addresses TABLE (Address nvarchar(50));
INSERT INTO @Addresses 
VALUES ('1627W.PINNA'),
       ('3000CLARCONAROADLOT252'),
       ('2633COURTLANDBLVD'),
       ('2633COURTLANDBLVD'),
       ('7113RDAVESOUTH'),
       ('1123Main Street')

;WITH CTE AS
(
    SELECT a.Address, w.Words, ISNULL(REPLACE(a.Address, w.Words,' ' + w.Words + ' '),a.Address) NewAddress
      FROM @Addresses a
      LEFT JOIN @Words w 
             ON CHARINDEX(w.Words,a.Address) > 0
)
,CTE2 AS(
    SELECT ISNULL(STUFF(CTE.NewAddress,PATINDEX('%[0-9][a-Z]%',CTE.NewAddress)+1,0,' '),CTE.NewAddress) NewAddress
    FROM CTE
)
SELECT REPLACE(ISNULL(STUFF(CTE.NewAddress,PATINDEX('%[a-Z][0-9]%',CTE.NewAddress)+1,0,' '), CTE.NewAddress),'  ',' ') NewAddress
FROM CTE2 CTE

Capture

frederico_fonseca SSCoach Points: 16154 More actions · Answer 5

yeahh.... and then it fails because of names that have the strings like this one "Southmoreland School District" or "northampton"

its a nearly impossible task without a table with exceptions and known values

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 6

Thanks this looks great. I only found away to take the first numbers off.

Thank you

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 7

itmasterw 60042

SSCrazy

Points: 2858

June 17, 2020 at 7:27 pm

#3763969

this really got us a lot closer.

thanks

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 8

itmasterw 60042 wrote:

Thanks this looks great. I only found away to take the first numbers off.

You really need a lot more test data, you can replace later numbers by adding more levels to the CTE and passing in the position of the last value found to act as the start position of the next search or maybe even making it a recursive CTE.

If you post some more test data I'll show you how you can increase the functionality of the query.

GaryV Hall of Fame Points: 3307 More actions · Answer 9

Once you get something you think is working, figure out how you would parse an address like

123 1/2 Railroad Street NW

if there were no spaces in it.

Good luck, but as others have said, it's a daunting task.