Separating an address

[Offspring22]

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?

[Hack]

What language are you using for the front end?

[Offspring22]

Um, it's all being done on MS SQL

[jacobsebastian]

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/