Split Address to Second Column

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 Address to Second Column

Author

Topic

C McFarland
Starting Member

9 Posts

Posted - 2012-06-01 : 12:57:09

Hello!

I have an Address column with the typical string as follows:

1234 Whatever Street Suite 123

My table has an Address2 column, and I'd like to write a query that would let me grab the word Suite and everything after it in the string and move it over to the Address2 column on the same row.

What do you think?

Christopher McFarland, MCTS
Professional Services Analyst
Office: 713-722-8778
Fax: 713-461-5134
Cell: 281-932-4329
www.zenoimaging.com

1080 West Sam Houston Pkwy North, Suite 120, Houston, TX 77043

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-01 : 13:17:57

Here is one way to split the colum, I'll leave the update up to you:

DECLARE @T TABLE (Address VARCHAR(50))

INSERT @T VALUES
('1234 Whatever Street Suite 123')

SELECT 
	*,
	SUBSTRING(Address, 0, CHARINDEX('Suite', Address)),
	SUBSTRING(Address, CHARINDEX('Suite', Address), LEN(Address)) -- LEN is not perfect, but it works
FROM 
	@T
WHERE 
	Address LIKE '%Suite%'

C McFarland
Starting Member

9 Posts

Posted - 2012-06-01 : 13:28:57

Ok, nice. Thanks for the post!

Where is it in there that the name of the column to recieve the split off data is defined?

Christopher McFarland, MCTS
Professional Services Analyst
Office: 713-722-8778
Fax: 713-461-5134
Cell: 281-932-4329
www.zenoimaging.com

1080 West Sam Houston Pkwy North, Suite 120, Houston, TX 77043

C McFarland
Starting Member

9 Posts

Posted - 2012-06-01 : 13:39:41

When I run the query on my table I get returns for any row containing the word Suite, but no split.

Wonder where I'm fouling it up?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-01 : 13:41:46

Can you post sample data in a consumable format to illustrate the issue?

Here are some links that can help you with that (if you are unfamilar):
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

C McFarland
Starting Member

9 Posts

Posted - 2012-06-01 : 14:23:26

I'm working on that now.

Christopher McFarland, MCTS
Professional Services Analyst
Office: 713-722-8778
Fax: 713-461-5134
Cell: 281-932-4329
www.zenoimaging.com

1080 West Sam Houston Pkwy North, Suite 120, Houston, TX 77043

C McFarland
Starting Member

9 Posts

Posted - 2012-06-01 : 14:52:27

OK, I came up with this for some workable data:

CREATE TABLE ADDRESS_SPLIT_TEST
(
Address varchar(50),
Address2 varchar(50)
)

INSERT INTO ADDRESS_SPLIT_TEST
(ADDRESS)

SELECT '1234 Whoknows Ave. Suite 123' UNION ALL
SELECT '4321 Whatever Dr. Suite 321' UNION ALL
SELECT '4132 Wow St. Suite 312' UNION ALL
SELECT '4123 Blah Blvd Suite 213'

When I run this against the resulting table:

SELECT
*,
SUBSTRING(Address, 0, CHARINDEX('Suite', Address)),
SUBSTRING(Address, CHARINDEX('Suite', Address), LEN(Address)) -- LEN is not perfect, but it works
FROM
ADDRESS_SPLIT_TEST
WHERE
Address LIKE '%Suite%'

I do get the split.

Nice!

Now I need to figure out how to get the data from the un-named column into my Address2 Column.

I understand that you've left that to me, which explains why there isn't a reference to Address2 in the query above.

Thanks for your help, and I'll dig into that problem right now.

Christopher McFarland, MCTS
Professional Services Analyst
Office: 713-722-8778
Fax: 713-461-5134
Cell: 281-932-4329
www.zenoimaging.com

1080 West Sam Houston Pkwy North, Suite 120, Houston, TX 77043

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources