Splitting a comma separated string in one field and populate several fields
-
I need to split the values of one field that has a variable number of names ( I have 10 receiving fields Trainer1, Trainer2, etc), e.g.:
Jacqueline Chazema, Lexa Banda, Joy Mwendwa, Felix Seixpence, Goliath Chiziba, Stephen Mwendwa .... each name needs to go into first 6 of 10 fields
LaVerne Hanes-Stevens, Veronica Hazel.... each name needs to go into first 2 of 10 fields
Ariana Arakelian, Rocío Cerna, Ana Zarina Fiorentini.... each name needs to go into first 3 of 10 fields
Rocio Cerna.... each name needs to go into 1 of first field
Eitan Kleinberg, Graciela Flores, Jennifer Legorreta, Marina Contreras, Patricia Garcia.... each name needs to go into first 5 of 10 fieldsI tried this and it didn't work:
SELECT [CourseTrainer],
PARSENAME(REPLACE([CourseTrainer],',','.'),1) 'CourseTrainer1',
PARSENAME(REPLACE([CourseTrainer],',','.'),2) 'CourseTrainer2',
PARSENAME(REPLACE([CourseTrainer],',','.'),3) 'CourseTrainer3',
PARSENAME(REPLACE([CourseTrainer],',','.'),4) 'CourseTrainer4',
PARSENAME(REPLACE([CourseTrainer],',','.'),5) 'CourseTrainer5',
PARSENAME(REPLACE([CourseTrainer],',','.'),6) 'CourseTrainer6',
PARSENAME(REPLACE([CourseTrainer],',','.'),7) 'CourseTrainer7',
PARSENAME(REPLACE([CourseTrainer],',','.'),8) 'CourseTrainer8',
PARSENAME(REPLACE([CourseTrainer],',','.'),9) 'CourseTrainer9',
PARSENAME(REPLACE([CourseTrainer],',','.'),10) 'CourseTrainer10'
FROM [MHF].[dbo].[MHFs] -
Use Jeff Moden's DelimitedSplit8K function: http://www.sqlservercentral.com/articles/Tally+Table/72993/
-
Since you are posting on the SQL Server 2012 forum, you may want to use this version.
😎 - Eirikur Eiriksson - Wednesday, August 8, 2018 11:36 PM
I wasn't aware of this version. I had been wondering if using LEAD/LAG would be able to improve the performance, but hadn't had a chance to sit down and experiment.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Thursday, August 9, 2018 10:22 AM
Because the internal optimized worktable which drives the logic, when the cardinality is less than the persisting limit (roughly 100k entries), the window functions fit very well for a non-max column, it can speed up the execution by bypassing the second scan of the string (charindex), roughly cutting the execution effort in half. In fact, as I tested, almost all of the original splitting functions gained drastically by implementing the LEAD function.
😎 - Eirikur Eiriksson - Wednesday, August 8, 2018 11:36 PM
For anyone interested, I tested this at Eirikur's request before he published it and it is, indeed, twice as fast as the current latest version of DelimitedSplit8k. Come on... say it with me... NASTY FAST!!!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Saturday, August 11, 2018 6:05 PM
He he, just about the same speed as a frozen pork chop leaves the launcher :exclamation:
😎 - briancampbellmcad - Wednesday, August 8, 2018 2:55 PM
Here's the how:
CREATE TABLE #TEST (
CHAR_STRING varchar(200)
);
INSERT INTO #TEST (CHAR_STRING)
VALUES ('Jacqueline Chazema, Lexa Banda, Joy Mwendwa, Felix Seixpence, Goliath Chiziba, Stephen Mwendwa'),
('LaVerne Hanes-Stevens, Veronica Hazel'),
('Ariana Arakelian, RocÃo Cerna, Ana Zarina Fiorentini'),
('Rocio Cerna'),
('Eitan Kleinberg, Graciela Flores, Jennifer Legorreta, Marina Contreras, Patricia Garcia');WITH ALL_DATA AS (
SELECT *
FROM #TEST AS T
CROSS APPLY ARIES.dbo.DelimitedSplit8K_LEAD(T.CHAR_STRING, ',') AS S
)
SELECT
[1] AS Trainer1,
[2] AS Trainer2,
[3] AS Trainer3,
[4] AS Trainer4,
[5] AS Trainer5,
[6] AS Trainer6,
[7] AS Trainer7,
[8] AS Trainer8,
[9] AS Trainer9,
[10] AS Trainer10
FROM ALL_DATA
PIVOT (MAX(Item) FOR ItemNumber IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])) AS PVTDROP TABLE #TEST;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply