Split comma seperated data into several fields on insert

I have two tables, one is a new Client table derived from an old User table.


[Client]
ClientId	int
Active		bit
CompanyName	varchar(30)
Title		varchar(20)
FirstName	varchar(30)
LastName	varchar(30)
Suffix		varchar(20)
AddrLine1	varchar(30)
AddrLine2	varchar(30)
AddrLine3	varchar(30)
Addrline4	varchar(30)
AddrCity	varchar(30)
AddrCounty	varchar(20)
AddrPostcode	varchar(8)
...

Open in new window


[User]
userID		uniqueidentifier
company		nvarchar(50)
title		nvarchar(50)
firstName	nvarchar(50)
lastName	nvarchar(50)
address1	nvarchar(100)
address2	nvarchar(100)
cityTown	nvarchar(50)
stateCounty	nvarchar(50)
postCode	nvarchar(50)
...

Open in new window


The old User Table has only got two Address fields and as a consequence there is a lot of data that looks like this:

Address1			Address2
Suite 2, Third Floor, 		The Old School House, 57 High Street
Velrag House			8 The Grange, Packington
3rd Floor			Hammer House, 113-117 Wardour Street 
4th Floor			Holborn Hall, 193-197 High Holborn
c/o Bedlam Artists Agency Ltd	384 Goldhawk Road, Hammersmith

Open in new window


I would like to insert the data into my new Client table so that it splits the data Like this:

AddrLine1			AddrLine2			AddrLine3			AddrLine4			
Suite 2 			Third Floor 			The Old School House 		57 High Street
Velrag House			8 The Grange			Packington			NULL
3rd Floor			Hammer House			113-117 Wardour Street		NULL
4th Floor			Holborn Hall			193-197 High Holborn		NULL
c/o Bedlam Artists Agency Ltd	384 Goldhawk Road		Hammersmith			NULL

Open in new window


The current (non working ) Insert statement looks like this:

INSERT INTO [DirectSpeakers].[dbo].[Client](
	Title,
	FirstName,
	Lastname,
	AddrLine1,
	AddrLine2,
	AddrLine3,
	AddrLine4,
	AddrCity,
	AddrCounty,
	AddrPostcode
	)
SELECT 
	NULLIF(title,''),
	NULLIF(firstname,''),
	NULLIF(lastname,''),
	NULLIF(address1,''),
	NULLIF(address2,''),
	NULL,
	NULL,
	NULLIF(citytown,''),
	NULLIF(stateCounty,''),
	NULLIF(postcode,'')
			
FROM Users 

Open in new window


Any help would be appreciated.

Kind regards,
LVL 2
splantonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

plusone3055Commented:
your really making your data redundant,
may I ask why you want 4 addresses ?
0
splantonAuthor Commented:
Firstly, and most importantly - because it is what the customer wants!
(it complies with the Standards for UK addressing...?)

RECIPIENT
[FLOOR] [APARTMENT]
[BUILDING]
[HOUSE_NUMBER] STREET_NAME
[DEPENDENT_LOCALITY]
LOCALITY
POSTAL_CODE
UNITED KINGDOM
0
ZberteocCommented:
I recommend you to create a function like this:
CREATE FUNCTION [dbo].[fnSplit]
( 
	@str varchar(max),
	@sep char(1)=' ',
	@i int=1
)	
RETURNS varchar(max)
as
/*******************************************************************************\
Function	: fnSplit

Purpose		: Parse a string in elements sepparated by a given 
			  character and return the element in position i
			  
Parameters	: @str - the string to parse
			  @sep - the separator character
			  @i   - the position of the element to be returned; 
					 if i<0 the function will return the number of elemets(size)
					 if i out of range the function will return NULL
Invoke		:
	
		select dbo.fnSplit('elem1 elem2 elem3',' ',2) 
		select dbo.fnSplit('elem1/elem2/elem3','/',3)
		select dbo.fnSplit('elem1/elem2/elem3','/',-1)

Author		: AdrianBT - 2013-03-11		
\*******************************************************************************/
begin
	-- declare variables	
	declare 
		@size int,
		@count int=1,
		@pos_start int=0,
		@pos_end int=0

	-- get the size of the array
	select 
		-- first, replace multiple spaces with one, just in case
		@str=replace(replace(replace(rtrim(ltrim(@str)),' ','`^'),'^`',''),'`^',' '),
		@size=LEN(@str)-LEN(replace(@str,@sep,''))+1

	-- if i negative return the size of the array
	if @i<0 
		-- return the size
		select
			@str=@size
	else	
	begin	

		-- if i out of range return null		
		if	@i not between 1 and @size 
				set @str=NULL
			else
			-- return the element in position i
			begin
			
				-- add an extra char to help with the last element
				select 
					@str=LTRIM(@str)+'^'
					
				-- loop the get the positions
				while @count<=@i
					select 
						@pos_start=@pos_end+1,
						@pos_end=CHARINDEX(@sep,@str,@pos_end+1),
						@count=@count+1
				
				-- isolate the element						
				select @str=	
					substring(
						@str, 
						@pos_start, 
						case 
							when @pos_end=0 then LEN(@str) 
							else @pos_end 
						end-@pos_start)
			end
	end
	
	-- return the element
	return @str
	
end	

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ZberteocCommented:
After that you use the function like this:

INSERT INTO [DirectSpeakers].[dbo].[Client](
	Title,
	FirstName,
	Lastname,
	AddrLine1,
	AddrLine2,
	AddrLine3,
	AddrLine4,
	AddrCity,
	AddrCounty,
	AddrPostcode
	)
SELECT 
	Title,
	FirstName,
	Lastname,
	dbo.fnSplit(Addr,',',1) as Addressline1,
	dbo.fnSplit(Addr,',',2) as Addressline2,
	dbo.fnSplit(Addr,',',3) as Addressline3,
	dbo.fnSplit(Addr,',',4) as Addressline4,
	AddrCity,
	AddrCounty,
	AddrPostcode
	
FROM
(
	SELECT 
		NULLIF(title,'') as Title,
		NULLIF(firstname,'') as FirstName,
		NULLIF(lastname,'') as Lastname,
		NULLIF(address1,'')+','+NULLIF(address2,'') as Addr,
		NULLIF(citytown,'') as AddrCity,
		NULLIF(stateCounty,'') as AddrCounty,
		NULLIF(postcode,'') as AddrPostcode
			
	FROM Users
) split 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
splantonAuthor Commented:
Thank you for one of the most clear, concise and unbelievably complete solutions to what was (for me) a very taxing issue.

Thank you.
0
ZberteocCommented:
:) I am glad I could help. That function is very useful in many string parsing situations.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.