[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 620
  • Last Modified:

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?
0
johntmcii
Asked:
johntmcii
  • 3
  • 2
1 Solution
 
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
 
reb73Commented:
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
 
johntmciiAuthor Commented:
Thanks, that's what I needed.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now