Parse field containing tab-delimited data into multiple fields

I have one field in a table that contains name and address data and within that one field, the name, address1, address2, city, state, ZIP are each separated by the tab character.  How do I parse the data from that one field into a temporary table containing separate fields for each portion of the name/address?
johntmciiAsked:
Who is Participating?
 
reb73Connect With a Mentor Commented:
Here's a CTE solution with a template table TmpDetails mirroring your table -
/*
if object_id('TmpDetails','U') IS NOT NULL
	drop table TmpDetails
--GO
create table TmpDetails (contactid int not null, contactdetails nvarchar(4000) null)
 
insert into TmpDetails
select 1, 'Aname	Aaddress1	Aaddress2	Acity	Astate	AZIP' union all
select 2, 'Bname	Baddress1		Bcity	Bstate	BZIP' union all
select 3, 'Cname			Ccity	Cstate	CZIP' union all
select 4, 'Dname				Dstate	' union all
select 5, 'Ename					'
--GO
*/ 
DECLARE @sep char(1)
SET	@sep = char(9)  -- Tab character!
 
;WITH CTE (Id, Value, Remnant, Field)
AS
(
        SELECT contactid, Code = contactdetails, Remnant = contactdetails, Field = 0
        FROM	TmpDetails
 
        UNION ALL
 
        SELECT  Id,
                Code = LEFT(Remnant, ISNULL(pos, remsize)),
                Remnant = RIGHT(Remnant, ISNULL(remsize - pos, 0)),
                Field = Field + 1
        FROM
	        (SELECT
	                pos = NULLIF(PATINDEX('%' + @sep + '%', Remnant), 0),
	                remsize = LEN(Remnant),
	                CTE.Id, CTE.Remnant, CTE.Field
	         FROM CTE
	         INNER JOIN TmpDetails on TmpDetails.contactid = CTE.Id
	         WHERE Remnant > ''
	        ) Tmp
)
-- Statement that executes the CTE
SELECT Id, [1] as Fld1,[2] as Fld2, [3] as Fld3, [4] as Fld4, [5] as Fld5
FROM (SELECT Id, Field, Value FROM CTE WHERE Field > 0) CTE
PIVOT (MAX(Value) FOR Field IN ([1],[2],[3],[4],[5])) PVT
ORDER BY Id
GO

Open in new window

0
 
reb73Commented:
You will need a UDF (user defined function) which splits a single varchar string and returns a table ..
0
 
reb73Commented:
What version is the SQL backend, if its SQL 2005 or later you can use a CTE solution..
0
 
johntmciiAuthor Commented:
SQL 2005
0
 
johntmciiAuthor Commented:
Thanks, that's what I needed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.