Parsing City, State, Zip - 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)

Parsing City, State, Zip

Author

Topic

rlopez
Starting Member

18 Posts

Posted - 2007-02-08 : 16:46:38

Is there a simplew ay to parse a field that contains the City, State and Zip, I have written functions to parse CSV's into a list, but I am just looking for a simple way to parse 'Miami, FL, 12345' into
Miami FL 12345

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-08 : 16:47:51

REPLACE(Col1,',',' ')

???

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rlopez
Starting Member

18 Posts

Posted - 2007-02-08 : 16:56:27

Actually what I would like to do is put City, State, Zip into three seperate columns. SO I guess I need to get anything before the first comma and between the second and third and then after the third.

City State Zip
Miami FL 12345

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-08 : 17:02:33

You'' need to use CHARINDEX and SUBSTRING

Perhaps PARSENAME

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-02-08 : 17:07:53

http://www.sqlteam.com/item.asp?ItemID=2652

www.elsasoft.org

Fatbug
Starting Member

5 Posts

Posted - 2007-02-14 : 20:52:47

A bit late but I just read the thread n thought this might be what you're after.

insert into <newtable>
select substring(<columnname>,1,charindex(',',<columname>,1)-1),
substring(<columname>,charindex(',',<columname>,1)+2,
charindex(',',<columname>,charindex(',',<columname>,1)+2) - charindex(',',<columname>,1) - 1),
substring(<columname>,charindex(',',<columname>,charindex(',',<columname>,1)+2)+2,len(<columname>) -
charindex(',',<columname>,charindex(',',<columname>,1)+2))
from <oldtable>

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-14 : 21:13:00

[code]
select dbo.f_GetEntryDelimiitted('Miami, FL, 12345', 1, ',', 'N'),
dbo.f_GetEntryDelimiitted('Miami, FL, 12345', 2, ',', 'N'),
dbo.f_GetEntryDelimiitted('Miami, FL, 12345', 3, ',', 'N')
[/code]
using Nigel's f_GetEntryDelimiitted from http://www.mindsdoor.net/SQLTsql/f_GetEntryDelimiitted.html

Kristen
Test

22859 Posts

Posted - 2007-02-15 : 05:38:52

If you can UPDATE in-situ in the table then this may do the trick:


DECLARE	@I1	int,
	@I2	int,
	@I3	int
UPDATE U
SET
	  @I1 = CHARINDEX(',', MyCol + ',')
	, [CITY] = LEFT(MyCol, @I1-1)
	, @I2 =  NullIf(CHARINDEX(',', MyCol + ',', @I1+1), 0)
	, [STATE] = SUBSTRING(MyCol, @I1+1, @I2-@I1-1)
	, @I3 =  NullIf(CHARINDEX(',', MyCol + ',', @I2+1), 0)
	, [ZIP] = SUBSTRING(MyCol, @I2+1, @I3-@I2-1)
FROM	dbo.MyTable AS U

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources