how to take out commas - 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 2000 Forums

SQL Server Development (2000)

how to take out commas

Author

Topic

yaditzal
Starting Member

22 Posts

Posted - 2010-04-08 : 13:16:06

hello.

I have this data sample

1 SW 129 AVENUE, STE 109
1 SW 129 AVENUE, STE 401
I need to make to columns addr1, addr2
addr2 has to be all after the comma

like this.
addr1 addr2

1 SW 129 AVENUE STE 109
1 SW 129 AVENUE STE 401

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 13:35:51

[code]SELECT LEFT(address,CHARINDEX(',',address)-1) AS addr1, SUBSTRING(address,CHARINDEX(',',address)+1,LEN(address)) as addr2
FROM Table[/code]

provided your address is consistent format with single ,

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

yaditzal
Starting Member

22 Posts

Posted - 2010-04-08 : 13:45:01

I'm geeting this error
Invalid length parameter passed to the SUBSTRING function.

I did it exactly the same like the query you gave me.

Kristen
Test

22859 Posts

Posted - 2010-04-08 : 16:21:39

I don't get an error here, Here's my code:


SELECT LEFT(address,CHARINDEX(',',address)-1) AS addr1, SUBSTRING(address,CHARINDEX(',',address)+1,LEN(address)) as addr2
FROM
(
	SELECT	'1 SW 129 AVENUE, STE 109' AS address UNION ALL
	SELECT	'1 SW 129 AVENUE, STE 401 '
) AS X

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources