Split out the Address Field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Split out the Address Field

Author

Topic

JezLisle
Posting Yak Master

132 Posts

Posted - 2010-01-20 : 10:49:39

I have an address field [Address 1] that has address input like this below

Address 1
Rehaghstown,
4 Castlefield
8 St Martins Ave
38 Rockfield Road
100 Retreat Park,
Gould,
1350 William Perse Terrace

I have used this below in my query to split out the address into 2 fields, but my problem is that I want just the numbers to appear in field1 and then the text side of the address into field2

SELECT
SUBSTRING([Address 1],0,CHARINDEX(' ',[Address 1])),
SUBSTRING([Address 1], (CHARINDEX(' ',[Address 1])+1),
LEN([Address 1])),
[Address 2],
[Address 3],
[Address 4],
[County],
FROM MyTable

How can I do it so that it splits how I am looking for and not this

Number Address1
Rehaghstown,
4 Castlefield
8 St Martins Ave
38 Rockfield Road
100 Retreat Park,
Gould,
1350 William Perse Terrace

I'm looking for this

Number Address1
Rehaghstown,
4 Castlefield
8 St Martins Ave
38 Rockfield Road
100 Retreat Park,
Gould,
1350 William Perse Terrace

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-20 : 11:05:34

quote:
Originally posted by JezLisle

I have an address field [Address 1] that has address input like this below

Address 1
Rehaghstown,
4 Castlefield
8 St Martins Ave
38 Rockfield Road
100 Retreat Park,
Gould,
1350 William Perse Terrace

I have used this below in my query to split out the address into 2 fields, but my problem is that I want just the numbers to appear in field1 and then the text side of the address into field2

SELECT
SUBSTRING([Address 1],0,CHARINDEX(' ',[Address 1])),
SUBSTRING([Address 1], (CHARINDEX(' ',[Address 1])+1),
LEN([Address 1])),
[Address 2],
[Address 3],
[Address 4],
[County],
FROM MyTable

How can I do it so that it splits how I am looking for and not this

Number Address1
Rehaghstown,
4 Castlefield
8 St Martins Ave
38 Rockfield Road
100 Retreat Park,
Gould,
1350 William Perse Terrace

I'm looking for this

Number Address1
Rehaghstown,
4 Castlefield
8 St Martins Ave
38 Rockfield Road
100 Retreat Park,
Gould,
1350 William Perse Terrace

You will get cautions against this 1st option, search the forums to see why, but if you know your data you can try something like this...

SELECT
Case when IsNumeric(Cast((SUBSTRING([Address 1],0,CHARINDEX(' ',[Address 1]))) as Int) = 1 then (SUBSTRING([Address 1],0,CHARINDEX(' ',[Address 1]))) else '' end as 1stPart,
Case when IsNumeric(Cast((SUBSTRING([Address 1],0,CHARINDEX(' ',[Address 1]))) as Int) = 1 then SUBSTRING([Address 1], (CHARINDEX(' ',[Address 1])+1) else [Address 1] end as 2ndPart ,
LEN([Address 1])),
[Address 2],
[Address 3],
[Address 4],
[County],
FROM MyTable

The better option is probably to check and see if the first substring IS LIKE '[0-9]%', if it is then do your substring formula, if it is not then leave field 1 blank. For field two you need to check again, if the 1st part is a number then you are good getting the 2nd part, if it is not then just return [Address 1]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources