splitting address in query - 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

General SQL Server Forums

New to SQL Server Programming

splitting address in query

Author

Topic

subhaoviya
Posting Yak Master

135 Posts

Posted - 2012-08-08 : 05:39:26

Hi,

i am having table column like address shown below

ADDRESS1
1 Abbey Drive
1a Abbey Drive
1b Abbey Drive
2 Abbey Drive
4 Abbey Drive
i need to split the house number seperately from address. expecting result set

Housenumber Address
1 Abbey Drive
1a Abbey Drive
...

thanks
subha

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-08 : 06:47:07

This is hard to do unless you are absolutely sure that there are no spaces in the house number. If there are none, then you can split it like this:

SELECT
	LEFT(ADDRESS1,CHARINDEX(' ',ADDRESS1)-1) AS HouseNumber,
	STUFF(ADDRESS1,1,CHARINDEX(' ',ADDRESS1),'') AS ADDRESS
FROM
	YourTable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 09:45:09

if its always two parts you can use


SELECT PARSENAME(REPLACE(Address,' ','.'),2) AS HouseNumber,
PARSENAME(REPLACE(Address,' ','.'),1) AS Address
FROM Table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-08 : 12:36:09

I admit it, I am biased against PARSENAME for a variety of reasons - one of which is this:

CREATE TABLE #tmp ([Address] VARCHAR(255));
INSERT INTO #tmp VALUES ('1973 Washington St.'), ('1973 Washington Mutual Street North')

SELECT PARSENAME(REPLACE(Address,' ','.'),2) AS HouseNumber,
PARSENAME(REPLACE(Address,' ','.'),1) AS Address
FROM #tmp

DROP TABLE #tmp;

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-08-16 : 10:30:52

Another technique


SELECT 
	LEFT(ADDRESS,CHARINDEX(' ',ADDRESS)-1) AS HOUSENUMBER,
	SUBSTRING(ADDRESS,PATINDEX('% [^ ]%',ADDRESS),LEN(ADDRESS)) AS ADDRESS
FROM #TMP

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources