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)
 Pull Comma Delimited Field to Insert into Table

Author  Topic 

CLEE25
Starting Member

14 Posts

Posted - 2008-12-02 : 19:25:18
Hello all,

Hoping someone might be able point me in the right direction.

I have a Student Table with Name, Address, etc. But it also has a field for school majors seperated by commas.

For example,

Nursing, FineArts
Theatre
Computer Science
Engineering, Fine Arts

I have trying to create a Stored Procedure, that will select this field, and if it has more then one major (ie a comma), will seperate them--so they can be inserted into a "major" table.

Ie, the above would be:
Nursing
Fine Arts
Theatre
Computer Science
Engineering
Fine Arts

Which I will then run a DISTINCT on so I can get a table with a row for every major.

This will be ongoing.

I have found some solutions with Substring, but they are all about passing comma deliminted as a parameter, not pulling them.

If anyone can post a link to a tutorial, or post some code, I would really, really appreciate it.

Thanks so much for your time!



PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-12-03 : 00:10:42
start with this
DECLARE @T_Table TABLE(Col VARCHAR(MAX), XMLCol XML)

INSERT INTO @T_Table(COl)
SELECT 'Nursing, FineArts' UNION ALL
SELECT 'Theatre' UNION ALL
SELECT 'Computer Science' UNION ALL
SELECT 'Engineering, Fine Arts'

UPDATE @T_Table
SET XMLCol = CAST('<d>' + REPLACE(Col, ',', '</d><d>') + '</d>' AS XML);

SELECT DISTINCT LTRIM(T2.Split.value('.', 'VARCHAR(100)')) AS 'Col'
FROM @T_Table
CROSS APPLY XMLCol.nodes('/d') as T2(Split)


"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 00:10:43
[code]SELECT s.Name,s.Address,f.Val
FROM Student s
CROSS APPLY dbo.ParseValues(s.majors)f[/code]


parsevalues function can be found below

[code]CREATE FUNCTION ParseValues
(@String varchar(8000),
@Delimiter char(1)
)
RETURNS @RESULTS TABLE
(ID int identity(1,1),
Val varchar(1000)
)
AS
BEGIN
DECLARE @Value varchar(100)

WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN LEFT(@String,CHARINDEX(@Delimiter,@String)-1) ELSE @String END,
@String=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN SUBSTRING(@String,CHARINDEX(@Delimiter,@String)+1,LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END[/code]
Go to Top of Page

CLEE25
Starting Member

14 Posts

Posted - 2008-12-03 : 11:49:16
Thanks so mcuh visakh16!

Works like a charm, if anyone is following this thread--just a quick correction. You need to pass along the seperation parameter as well for example:


SELECT s.Name,s.Address,f.Val
FROM Student s
CROSS APPLY dbo.ParseValues(s.majors,',')f


Thanks again to all of you!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 11:59:16
quote:
Originally posted by CLEE25

Thanks so mcuh visakh16!

Works like a charm, if anyone is following this thread--just a quick correction. You need to pass along the seperation parameter as well for example:


SELECT s.Name,s.Address,f.Val
FROM Student s
CROSS APPLY dbo.ParseValues(s.majors,',')f


Thanks again to all of you!


You're welcome
Nice catch too
Go to Top of Page

Fencer
Starting Member

1 Post

Posted - 2009-06-12 : 13:54:22
The above solution looks exactly like what I have been looking for.



I can not save either the Select Statement or the Function and I think it is because the dbo.ParseValues doesn't exit yet for the select statement.

When I try to paste in the Function, I get an error that variable 'Sting' is not defined and variable 'Delimiter' is not defined
What am I missing?


This is in regards to visakh16 solution.

Thank you!



Fence
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-14 : 02:10:01
quote:
Originally posted by Fencer

The above solution looks exactly like what I have been looking for.



I can not save either the Select Statement or the Function and I think it is because the dbo.ParseValues doesn't exit yet for the select statement.

When I try to paste in the Function, I get an error that variable 'Sting' is not defined and variable 'Delimiter' is not defined
What am I missing?


This is in regards to visakh16 solution.

Thank you!



Fence


can you show the code please? it seems like you've done a typo...it string not sting
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-06-14 : 19:54:48
No need for functions, XML, or intermediate tables.

--===== Create and populate a test table.
-- This is NOT part of the solution.

DECLARE @TestTable TABLE(Majors VARCHAR(8000))
INSERT INTO @TestTable(Majors)
SELECT 'Nursing, FineArts' UNION ALL
SELECT 'Theatre' UNION ALL
SELECT 'Computer Science' UNION ALL
SELECT 'Engineering, Fine Arts' UNION ALL
SELECT 'Fine Arts, Engineering, Computer Science, Mathematics'

--===== Return the distinct list of majors without the use of an intermediate table.
SELECT DISTINCT
SUBSTRING(l.List, v.Number+1, CHARINDEX(',', l.List, v.Number+1) -v.Number-1) AS Major
FROM Master.dbo.spt_Values v
CROSS JOIN
(SELECT ',' + REPLACE(Majors, ', ', ',') + ',' AS List FROM @TestTable) l
WHERE v.Type = 'P'
AND v.Number BETWEEN 1 AND LEN(l.List)-1
AND SUBSTRING(l.List, v.Number, 1)=','

If you need to split more than 2047 characters wide per row, post back and we'll fix you up.

--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

For better, quicker answers, click on the following... [url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
Go to Top of Page

f3504x4ger
Starting Member

12 Posts

Posted - 2009-06-30 : 16:14:16
Hi,

I am doing something similar, but mine varies slightly/significantly.

I will have a single column of data that will contain concatenated information for an address separated by commas. This will include the following fields concatenated into one field separated by commaas with: address1, address2, city, state, zip.

I then need to take this single field and insert the five different parts of the single field into 5 different columns in a table. The examples shown, have them going to the same column in the same table.

I appreciate any help or information anyone can provide.

Thanks!
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-06-30 : 17:45:04
What's the primary key for that table?

--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Go to Top of Page

f3504x4ger
Starting Member

12 Posts

Posted - 2009-07-01 : 09:41:03
Jeff,

The Primary Key is AddressId which is an identity column. Thanks

quote:
Originally posted by Jeff Moden

What's the primary key for that table?

--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 12:28:23
quote:
Originally posted by f3504x4ger

Hi,

I am doing something similar, but mine varies slightly/significantly.

I will have a single column of data that will contain concatenated information for an address separated by commas. This will include the following fields concatenated into one field separated by commaas with: address1, address2, city, state, zip.

I then need to take this single field and insert the five different parts of the single field into 5 different columns in a table. The examples shown, have them going to the same column in the same table.

I appreciate any help or information anyone can provide.

Thanks!


why are you storing data like this in table? have you heard about normalisation?
secondly, does all your rows have all the five column values? if not, will there be blank spaces for missing ones or NULL?
Go to Top of Page

f3504x4ger
Starting Member

12 Posts

Posted - 2009-07-01 : 14:21:35
This column with the concatenated fields is part of a table that is being used for user approvals. This field with the 5 fields concatenated is the new value that needs approved or rejected by the user. Once the user approves it, I have to take the field apart and then store the 5 concatenated values into 5 different columns(address1, address2, city, state, zip). All of my tables I am using are part of a normalized database. Yes, there could also be a blank('') value in one of the five concatenated fields. If it comes into my database as a null value, I insert it into my table as a blank ''.

quote:
Originally posted by visakh16

quote:
Originally posted by f3504x4ger

Hi,

I am doing something similar, but mine varies slightly/significantly.

I will have a single column of data that will contain concatenated information for an address separated by commas. This will include the following fields concatenated into one field separated by commaas with: address1, address2, city, state, zip.

I then need to take this single field and insert the five different parts of the single field into 5 different columns in a table. The examples shown, have them going to the same column in the same table.

I appreciate any help or information anyone can provide.

Thanks!


why are you storing data like this in table? have you heard about normalisation?
secondly, does all your rows have all the five column values? if not, will there be blank spaces for missing ones or NULL?

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 14:25:54
so there will be always five placeholders (value or blank) seperated by commas?
Go to Top of Page

f3504x4ger
Starting Member

12 Posts

Posted - 2009-07-01 : 15:02:34
Yes, there will always be five. Sorry for the confusion.

quote:
Originally posted by visakh16

so there will be always five placeholders (value or blank) seperated by commas?

Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-07-02 : 08:19:22
quote:
Originally posted by f3504x4ger

Yes, there will always be five. Sorry for the confusion.

quote:
Originally posted by visakh16

so there will be always five placeholders (value or blank) seperated by commas?





My apologies... I got pretty busy yesterday and I'm on my way to work, but wanted to let you know I can help on this. I do like to test my code before posting, though. To that end, would you post a Create Table statement and provide some test data in the form of an INSERT/SELECT UNION ALL statement just to save me a bit of time? Your request is a pretty easy thing to do and we don't need a function or a While Loop to do it but, like I said, I like to test before posting any code and your test data would be a big help.

--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Go to Top of Page

f3504x4ger
Starting Member

12 Posts

Posted - 2009-07-02 : 15:06:45
Thanks Jeff. No problem. I appreciate you looking into this. I was also busy this morning and didn't get a chance to do this until now. I created a small temp table and a target temp table. Thanks again! I'm on vacation next week, so I won't be working on this again unitl the 13th. Just letting you know in case you don't see any response from me. Have a good 4th of July! Gerry

IF OBJECT_ID ('tempdb..#ProcessQueue') is NOT NULL
DROP TABLE #ProcessQueue
create table #ProcessQueue
(
SeqId int,
AcceptOrReject char(1),
NewValue varchar(100)
)

insert into #ProcessQueue (SeqId, AcceptOrReject, NewValue) Values (1, 'A', '123 First St//Anytown/OH/12345')
insert into #ProcessQueue (SeqId, AcceptOrReject, NewValue) Values (2, 'A', '456 2nd St//Smith Town/PA/67890')
insert into #ProcessQueue (SeqId, AcceptOrReject, NewValue) Values (3, 'A', '89647 Main Street/Suite A/Morgan/WV/56978')
insert into #ProcessQueue (SeqId, AcceptOrReject, NewValue) Values (4, 'A', '65477 River Styx Road/Apt 1/Los Angeles/CA/96845')
insert into #ProcessQueue (SeqId, AcceptOrReject, NewValue) Values (5, 'A', '968 Small Blvd/Office 2/Bigtown/NV/06971')
insert into #ProcessQueue (SeqId, AcceptOrReject, NewValue) Values (6, 'A', '966345 Center Street//New York/NY/69935')
insert into #ProcessQueue (SeqId, AcceptOrReject, NewValue) Values (7, 'A', '321 Last St//Smalltown/FL/96987')
insert into #ProcessQueue (SeqId, AcceptOrReject, NewValue) Values (8, 'A', '369988 Big Circle//Yourtown/TX/26978')
insert into #ProcessQueue (SeqId, AcceptOrReject, NewValue) Values (9, 'A', '123 Small Square Avenue//Yourtown/OH/65854')
insert into #ProcessQueue (SeqId, AcceptOrReject, NewValue) Values (10, 'A', '963 Canal Blvd //Newton Falls/OH/44444')

IF OBJECT_ID ('tempdb..#TargetTable') is NOT NULL
DROP TABLE #TargetTable
create table #TargetTable
(
SeqId int,
Address1 varchar(100),
Address2 varchar(100),
City varchar(100),
State char(2),
Zip char(9)
)

quote:
Originally posted by Jeff Moden

quote:
Originally posted by f3504x4ger

Yes, there will always be five. Sorry for the confusion.

quote:
Originally posted by visakh16

so there will be always five placeholders (value or blank) seperated by commas?





My apologies... I got pretty busy yesterday and I'm on my way to work, but wanted to let you know I can help on this. I do like to test my code before posting, though. To that end, would you post a Create Table statement and provide some test data in the form of an INSERT/SELECT UNION ALL statement just to save me a bit of time? Your request is a pretty easy thing to do and we don't need a function or a While Loop to do it but, like I said, I like to test before posting any code and your test data would be a big help.

--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"



Go to Top of Page

f3504x4ger
Starting Member

12 Posts

Posted - 2009-07-02 : 15:08:56
Jeff, I also forgot to mention that instead of comma delimiters, there will be a forward slash / used as the delimiter. We found there were some commas in the actual address data. thanks

quote:
Originally posted by f3504x4ger

Thanks Jeff. No problem. I appreciate you looking into this. I was also busy this morning and didn't get a chance to do this until now. I created a small temp table and a target temp table. Thanks again! I'm on vacation next week, so I won't be working on this again unitl the 13th. Just letting you know in case you don't see any response from me. Have a good 4th of July! Gerry

IF OBJECT_ID ('tempdb..#ProcessQueue') is NOT NULL
DROP TABLE #ProcessQueue
create table #ProcessQueue
(
SeqId int,
AcceptOrReject char(1),
NewValue varchar(100)
)

insert into #ProcessQueue (SeqId, AcceptOrReject, NewValue) Values (1, 'A', '123 First St//Anytown/OH/12345')
insert into #ProcessQueue (SeqId, AcceptOrReject, NewValue) Values (2, 'A', '456 2nd St//Smith Town/PA/67890')
insert into #ProcessQueue (SeqId, AcceptOrReject, NewValue) Values (3, 'A', '89647 Main Street/Suite A/Morgan/WV/56978')
insert into #ProcessQueue (SeqId, AcceptOrReject, NewValue) Values (4, 'A', '65477 River Styx Road/Apt 1/Los Angeles/CA/96845')
insert into #ProcessQueue (SeqId, AcceptOrReject, NewValue) Values (5, 'A', '968 Small Blvd/Office 2/Bigtown/NV/06971')
insert into #ProcessQueue (SeqId, AcceptOrReject, NewValue) Values (6, 'A', '966345 Center Street//New York/NY/69935')
insert into #ProcessQueue (SeqId, AcceptOrReject, NewValue) Values (7, 'A', '321 Last St//Smalltown/FL/96987')
insert into #ProcessQueue (SeqId, AcceptOrReject, NewValue) Values (8, 'A', '369988 Big Circle//Yourtown/TX/26978')
insert into #ProcessQueue (SeqId, AcceptOrReject, NewValue) Values (9, 'A', '123 Small Square Avenue//Yourtown/OH/65854')
insert into #ProcessQueue (SeqId, AcceptOrReject, NewValue) Values (10, 'A', '963 Canal Blvd //Newton Falls/OH/44444')

IF OBJECT_ID ('tempdb..#TargetTable') is NOT NULL
DROP TABLE #TargetTable
create table #TargetTable
(
SeqId int,
Address1 varchar(100),
Address2 varchar(100),
City varchar(100),
State char(2),
Zip char(9)
)

quote:
Originally posted by Jeff Moden

quote:
Originally posted by f3504x4ger

Yes, there will always be five. Sorry for the confusion.

quote:
Originally posted by visakh16

so there will be always five placeholders (value or blank) seperated by commas?





My apologies... I got pretty busy yesterday and I'm on my way to work, but wanted to let you know I can help on this. I do like to test my code before posting, though. To that end, would you post a Create Table statement and provide some test data in the form of an INSERT/SELECT UNION ALL statement just to save me a bit of time? Your request is a pretty easy thing to do and we don't need a function or a While Loop to do it but, like I said, I like to test before posting any code and your test data would be a big help.

--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"





Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-07-03 : 18:16:27
f3504x4ger,

Thanks for posting the CREATE TABLE scripts and for posting the test data in a readily consumable format. Makes it a whole lot easier for me to help because it answers any and all questions I may have about your tables and data as well as making it so I don't have to spend a whole lot of time setting up a test harness.

Here's the complete solution including the test harness. I hope this is what you were looking for.
--=============================================================================================
-- Create a Tally table. If you don't already have one, then this is considered to
-- be a part of the solution.
--=============================================================================================
--===== If a Tally table doesn't already exist, build one
IF OBJECT_ID('dbo.Tally','U') IS NULL
BEGIN
--===== Create the tally table and populate it on-the-fly
SELECT TOP 11000 ISNULL(ROW_NUMBER() OVER (ORDER BY GETDATE()),0) AS N
INTO dbo.Tally
FROM Master.sys.All_Columns ac1
CROSS JOIN
Master.sys.All_Columns ac2

--===== Add a Clustered PK for speed
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Let everyone use it
GRANT SELECT ON dbo.Tally TO PUBLIC
END

--=============================================================================================
-- Setup a test harness. Note that this is NOT a part of the solution.
--=============================================================================================
--===== Conditionally drop the test tables so we can easily do reruns for testing.
IF OBJECT_ID ('TempDB..#ProcessQueue') is NOT NULL
DROP TABLE #ProcessQueue

IF OBJECT_ID ('TempDB..#TargetTable') is NOT NULL
DROP TABLE #TargetTable

--===== Create the test tables
CREATE TABLE #ProcessQueue
(
SeqId INT,
AcceptOrReject CHAR(1),
NewValue VARCHAR(100)
)

CREATE TABLE #TargetTable
(
SeqId INT,
Address1 VARCHAR(100),
Address2 VARCHAR(100),
City VARCHAR(100),
State CHAR(2),
Zip CHAR(9)
)

--===== Populate the source table with known test data
INSERT INTO #ProcessQueue
(SeqID, AcceptOrReject, NewValue)
SELECT 1, 'A', '123 First St//Anytown/OH/12345' UNION ALL
SELECT 2, 'A', '456 2nd St//Smith Town/PA/67890' UNION ALL
SELECT 3, 'A', '89647 Main Street/Suite A/Morgan/WV/56978' UNION ALL
SELECT 4, 'A', '65477 River Styx Road/Apt 1/Los Angeles/CA/96845' UNION ALL
SELECT 5, 'A', '968 Small Blvd/Office 2/Bigtown/NV/06971' UNION ALL
SELECT 6, 'A', '966345 Center Street//New York/NY/69935' UNION ALL
SELECT 7, 'A', '321 Last St//Smalltown/FL/96987' UNION ALL
SELECT 8, 'A', '369988 Big Circle//Yourtown/TX/26978' UNION ALL
SELECT 9, 'A', '123 Small Square Avenue//Yourtown/OH/65854' UNION ALL
SELECT 10, 'A', '963 Canal Blvd //Newton Falls/OH/44444'


--=============================================================================================
-- Solve the problem. The CTE does the "split" in conjuction with the tally table and
-- the outer query repivots the data back to horizontal form to properly populate the
-- target table. Such a cross-tab is actually faster than a pivot in most cases.
--
-- For more info on the Tally (or Numbers) table, please see the following:
-- http://www.sqlservercentral.com/articles/T-SQL/62867/
--
-- For more info on Cross-Tabs and Pivots including performance stats, please see:
-- http://www.sqlservercentral.com/articles/T-SQL/63681/
--
-- Jeff Moden
--=============================================================================================
;WITH
cteSplit AS
(
SELECT pq.SeqID,
ROW_NUMBER() OVER (PARTITION BY pq.SeqID ORDER BY t.N) AS AddressPart,
SUBSTRING('/' + pq.NewValue, N+1, CHARINDEX('/', pq.NewValue + '/', N)-N) AS SplitValue
FROM dbo.Tally t
CROSS JOIN #ProcessQueue pq
WHERE t.N <= LEN('/' + pq.NewValue)
AND SUBSTRING('/' + pq.NewValue, N, 1) = '/'
)
INSERT INTO #TargetTable
(SeqID, Address1, Address2, City, State, Zip)
SELECT s.SeqID,
MAX(CASE WHEN s.AddressPart = 1 THEN SplitValue END) AS Address1,
MAX(CASE WHEN s.AddressPart = 2 THEN SplitValue END) AS Address2,
MAX(CASE WHEN s.AddressPart = 3 THEN SplitValue END) AS City,
MAX(CASE WHEN s.AddressPart = 4 THEN SplitValue END) AS State,
MAX(CASE WHEN s.AddressPart = 5 THEN SplitValue END) AS Zip
FROM cteSplit s
GROUP BY s.SeqID

--===== Display the result from the target table
SELECT * FROM #TargetTable



--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Go to Top of Page

f3504x4ger
Starting Member

12 Posts

Posted - 2009-07-14 : 11:06:28
Jeff,

Thank you so much for your response and the code! It is exactly what I was looking for. I modified some of the code to work with what I am doing and it helped me uncover some errors with the source data. Once the source data is "cleaned up", this code will work and do exactly what I was looking for.

I appreciate you taking the time to read my post and help me out.

Gerry

quote:
Originally posted by Jeff Moden

f3504x4ger,

Thanks for posting the CREATE TABLE scripts and for posting the test data in a readily consumable format. Makes it a whole lot easier for me to help because it answers any and all questions I may have about your tables and data as well as making it so I don't have to spend a whole lot of time setting up a test harness.

Here's the complete solution including the test harness. I hope this is what you were looking for.
--=============================================================================================
-- Create a Tally table. If you don't already have one, then this is considered to
-- be a part of the solution.
--=============================================================================================
--===== If a Tally table doesn't already exist, build one
IF OBJECT_ID('dbo.Tally','U') IS NULL
BEGIN
--===== Create the tally table and populate it on-the-fly
SELECT TOP 11000 ISNULL(ROW_NUMBER() OVER (ORDER BY GETDATE()),0) AS N
INTO dbo.Tally
FROM Master.sys.All_Columns ac1
CROSS JOIN
Master.sys.All_Columns ac2

--===== Add a Clustered PK for speed
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Let everyone use it
GRANT SELECT ON dbo.Tally TO PUBLIC
END

--=============================================================================================
-- Setup a test harness. Note that this is NOT a part of the solution.
--=============================================================================================
--===== Conditionally drop the test tables so we can easily do reruns for testing.
IF OBJECT_ID ('TempDB..#ProcessQueue') is NOT NULL
DROP TABLE #ProcessQueue

IF OBJECT_ID ('TempDB..#TargetTable') is NOT NULL
DROP TABLE #TargetTable

--===== Create the test tables
CREATE TABLE #ProcessQueue
(
SeqId INT,
AcceptOrReject CHAR(1),
NewValue VARCHAR(100)
)

CREATE TABLE #TargetTable
(
SeqId INT,
Address1 VARCHAR(100),
Address2 VARCHAR(100),
City VARCHAR(100),
State CHAR(2),
Zip CHAR(9)
)

--===== Populate the source table with known test data
INSERT INTO #ProcessQueue
(SeqID, AcceptOrReject, NewValue)
SELECT 1, 'A', '123 First St//Anytown/OH/12345' UNION ALL
SELECT 2, 'A', '456 2nd St//Smith Town/PA/67890' UNION ALL
SELECT 3, 'A', '89647 Main Street/Suite A/Morgan/WV/56978' UNION ALL
SELECT 4, 'A', '65477 River Styx Road/Apt 1/Los Angeles/CA/96845' UNION ALL
SELECT 5, 'A', '968 Small Blvd/Office 2/Bigtown/NV/06971' UNION ALL
SELECT 6, 'A', '966345 Center Street//New York/NY/69935' UNION ALL
SELECT 7, 'A', '321 Last St//Smalltown/FL/96987' UNION ALL
SELECT 8, 'A', '369988 Big Circle//Yourtown/TX/26978' UNION ALL
SELECT 9, 'A', '123 Small Square Avenue//Yourtown/OH/65854' UNION ALL
SELECT 10, 'A', '963 Canal Blvd //Newton Falls/OH/44444'


--=============================================================================================
-- Solve the problem. The CTE does the "split" in conjuction with the tally table and
-- the outer query repivots the data back to horizontal form to properly populate the
-- target table. Such a cross-tab is actually faster than a pivot in most cases.
--
-- For more info on the Tally (or Numbers) table, please see the following:
-- http://www.sqlservercentral.com/articles/T-SQL/62867/
--
-- For more info on Cross-Tabs and Pivots including performance stats, please see:
-- http://www.sqlservercentral.com/articles/T-SQL/63681/
--
-- Jeff Moden
--=============================================================================================
;WITH
cteSplit AS
(
SELECT pq.SeqID,
ROW_NUMBER() OVER (PARTITION BY pq.SeqID ORDER BY t.N) AS AddressPart,
SUBSTRING('/' + pq.NewValue, N+1, CHARINDEX('/', pq.NewValue + '/', N)-N) AS SplitValue
FROM dbo.Tally t
CROSS JOIN #ProcessQueue pq
WHERE t.N <= LEN('/' + pq.NewValue)
AND SUBSTRING('/' + pq.NewValue, N, 1) = '/'
)
INSERT INTO #TargetTable
(SeqID, Address1, Address2, City, State, Zip)
SELECT s.SeqID,
MAX(CASE WHEN s.AddressPart = 1 THEN SplitValue END) AS Address1,
MAX(CASE WHEN s.AddressPart = 2 THEN SplitValue END) AS Address2,
MAX(CASE WHEN s.AddressPart = 3 THEN SplitValue END) AS City,
MAX(CASE WHEN s.AddressPart = 4 THEN SplitValue END) AS State,
MAX(CASE WHEN s.AddressPart = 5 THEN SplitValue END) AS Zip
FROM cteSplit s
GROUP BY s.SeqID

--===== Display the result from the target table
SELECT * FROM #TargetTable



--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"



Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-07-14 : 11:38:39
You bet, Gerry. Thanks for the feedback.

--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Go to Top of Page
    Next Page

- Advertisement -