How to sperate an address without spces

  • 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

     

  • 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

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

    Thank you

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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

  • 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

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

    Thank you

  • this really got us  a lot closer.

    thanks

  • 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.

  • 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.

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

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