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..!!"

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

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!

---

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

---

--===== 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)=','

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"

---

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!

---

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?

---

quote:

---

Originally posted by visakh16

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

---

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?

---

---

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"

---

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"

---

---

--=============================================================================================
--      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

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"

---