Split/Parse column/row data into multiple fields or 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 2000 Forums

SQL Server Development (2000)

Split/Parse column/row data into multiple fields or column

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-11-13 : 08:03:31

Sly writes "Dear Sql Team,
I have column name "billto" with sample data below:

---------------------------------------------------
Jane Doe, 1322 S. Ridgeland, Berwin, IL 60402
Janet Doe, 176 Hampton Drive, Langhorg, PA 19047
Jil Doe, 4831 E. San Gabriel, Fresno, CA 93726
Sam Doe, 11913 Brookshire Avenue, Downey, CA 90242
Harry Doe, 4240 2G Hutchinson River Parkway, Bronx, NY 10475
---------------------------

I used the sp_ParseArray created by graz@sqlteam.com to try and split the fields but to no avail.

Is there a way I could specify the column-name I want to parse and the delimited character?

Thanks in advance

Sly

SQL Server 2000
Windows 2000 Server"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-11-13 : 08:16:51

What's wrong with using the http://www.sqlteam.com/item.asp?ItemID=15044 from the main page of SQLTeam.com?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-13 : 20:22:24

or some really butchered version of it...

declare  @t1 table  ( billto char(100))

insert into @t1 
select 'Jane Doe, 1322 S. Ridgeland, Berwin, IL 60402' union
select 'Janet Doe, 176 Hampton Drive, Langhorg, PA 19047' union
select 'Jil Doe, 4831 E. San Gabriel, Fresno, CA 93726' union
select 'Sam Doe, 11913 Brookshire Avenue, Downey, CA 90242' union
select 'Harry Doe, 4240 2G Hutchinson River Parkway, Bronx, NY 10475'

select 
	left(parsename(replace(replace(billto,'.','|'),',','.'),4),charindex(' ',parsename(replace(replace(billto,'.','|'),',','.'),4)) ) firstname ,
	ltrim(substring(parsename(replace(replace(billto,'.','|'),',','.'),4),charindex(' ',parsename(replace(replace(billto,'.','|'),',','.'),4)), len(parsename(replace(replace(billto,'.','|'),',','.'),4)))) lastname,
	ltrim(replace(parsename(replace(replace(billto,'.','|'),',','.'),3),'|','.')) address,
	ltrim(parsename(replace(replace(billto,'.','|'),',','.'),2)) city,
	left(ltrim(parsename(replace(replace(billto,'.','|'),',','.'),1)),charindex(' ',ltrim(parsename(replace(replace(billto,'.','|'),',','.'),1))) ) state ,
	ltrim(substring(ltrim(parsename(replace(replace(billto,'.','|'),',','.'),1)),charindex(' ',ltrim(parsename(replace(replace(billto,'.','|'),',','.'),1))), len(ltrim(parsename(replace(replace(billto,'.','|'),',','.'),1))))) zipcode
from @t1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources