Results 1 to 4 of 4

Thread: Parsing Address

  1. #1
    Join Date
    Feb 2004
    Posts
    3

    Unanswered: Parsing Address

    Parsing Address
    This is not really a reply, but I saw the problem and the replies look very promissing.
    I'm using ss2k, I have a table with an address column.
    here is some example of the records under ADDRESS :

    WILLOW CREEK PL
    RED BARN DR
    RED BARN DR
    CARRINGTON DR
    RENNER RD
    EDMONTON CT
    SPRINGBRANCH DR
    HILLROSE DR
    CEDAR RIDGE DR
    LARTAN TRL
    PRESIDENT GEORGE BUSH HWY

    What I want to do is to write script that runs daily and parse the street names (RED BARN) and street types (Dr, PL , etc.. ) to 2 colums. As u can see there is no fixed length or fixed number of words ...etc ...
    Any help would be really appreciated.
    thnks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    From the earlier with some changes...again the last pieces has to be the address type...if there are no spaces you're hosed

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 varchar(255), Col2 varchar(255), Col3 varchar(50))
    GO
    
    INSERT INTO myTable99(Col1)
    SELECT 'WILLOW CREEK PL' UNION ALL
    SELECT 'RED BARN DR' UNION ALL
    SELECT 'RED BARN DR' UNION ALL
    SELECT 'CARRINGTON DR' UNION ALL
    SELECT 'RENNER RD' UNION ALL
    SELECT 'EDMONTON CT' UNION ALL
    SELECT 'SPRINGBRANCH DR' UNION ALL
    SELECT 'HILLROSE DR' UNION ALL
    SELECT 'CEDAR RIDGE DR' UNION ALL
    SELECT 'LARTAN TRL' UNION ALL
    SELECT 'BRETTSWAY' UNION ALL
    SELECT 'PRESIDENT GEORGE BUSH HWY'
    GO
    
    SELECT * FROM myTable99
    GO
    
    UPDATE t
       SET	  Col2 = REVERSE(SUBSTRING(REVERSE(Col1)
    				,(CHARINDEX(' ',REVERSE(Col1))+1)
    				,LEN(Col1)-CHARINDEX(' ',REVERSE(Col1))))
    	, Col3 = REVERSE(SUBSTRING(REVERSE(Col1)
    				,1
    				,(CHARINDEX(' ',REVERSE(Col1))-1)))
      FROM myTable99 t
     WHERE CHARINDEX(' ',Col1)<>0
    GO
    
    -- Show rows that did not Translate
    
    SELECT * FROM myTable99 WHERE Col2 IS NULL Or Col3 IS NULL
    
    -- And rows that did
    
    SELECT * FROM myTable99 WHERE Col2 IS NOT NULL AND Col3 IS NOT NULL
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Posts
    3

    still trying

    Every time I get new error. this time its the length:
    "Invalid length parameter passed to the substring function."

    see attached file ...
    Attached Files Attached Files

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Change the WHERE Clause to this...

    WHERE CHARINDEX(' ',Col1)>1

    And see what happens...

    What it means though, is your seeing a space in the first reversed position

    You'll need to use LTRIM and RTRIM Perhaps...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •