Separating an address


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Separating an address

  1. #1
    Join Date
    Feb 2009
    Posts
    2

    Separating an address

    So I have a table, and in this table is a field "address", which contains address, IE "742 Evergreen Terrace", although some also have a street direction "742 Evergreen Terrace NE".

    I need to get these into seperate fields House_Num, Street_Name, Street_Type, and Street_Dir.

    Anyone have any tips on how to go about this?

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    What language are you using for the front end?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  3. #3
    Join Date
    Feb 2009
    Posts
    2
    Um, it's all being done on MS SQL

  4. #4
    Join Date
    Jan 2009
    Posts
    4
    You have not specified the version of SQL server you are using. Assuming that you are on SQL server 2005, the following will work.

    DECLARE @t TABLE (address VARCHAR(100))
    INSERT INTO @t(address) SELECT '742 Evergreen Terrace'
    INSERT INTO @t(address) SELECT '742 Evergreen Terrace NE'

    SELECT
    x.value('i[1]','VARCHAR(15)') AS House_number,
    x.value('i[2]','VARCHAR(15)') AS Street_name,
    x.value('i[3]','VARCHAR(15)') AS Street_Type,
    x.value('i[4]','VARCHAR(15)') AS Street_dir
    FROM(
    SELECT
    CAST('<a><i>' + REPLACE(address, ' ','</i><i>') + '</i></a>' AS XML) AS ad
    FROM @t
    ) t
    CROSS APPLY ad.nodes('/a') a(x)
    /*
    House_number Street_name Street_Type Street_dir
    --------------- --------------- --------------- ---------------
    742 Evergreen Terrace NULL
    742 Evergreen Terrace NE
    */

    Jacob Sebastian, SQL Server MVP
    http://blog.beyondrelational.com/

Similar Threads

  1. Gateway Address
    By annlinnet in forum .NET
    Replies: 1
    Last Post: 08-11-2008, 08:40 AM
  2. Replies: 18
    Last Post: 12-13-2006, 03:49 PM
  3. Replies: 1
    Last Post: 08-10-2006, 01:44 AM
  4. Replies: 1
    Last Post: 04-07-2006, 06:21 AM
  5. Replies: 0
    Last Post: 01-30-2006, 02:13 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center