Split a pipe delimited string into two columns

Question

Split a pipe delimited string into two columns

Moe_Szyslak

SSC Veteran

Points: 217
More actions
March 6, 2014 at 12:57 pm

#289123

Hello all,
I have a single string "XYZ00001|Test_b|XYZ00002|Test_a|XYZ00003|Test_c" that will continue to grow over time.
Is there a way I can extract the values from the string into two separate columns?
XYZ0001 Test_b
XYZ0002 Test_a
XYZ0003 Test_c
Any advice or suggestions is appreciated. Thanks in advance!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Moe_Szyslak (3/6/2014)
Hello all,
I have a single string "XYZ00001|Test_b|XYZ00002|Test_a|XYZ00003|Test_c" that will continue to grow over time.
Is there a way I can extract the values from the string into two separate columns?
XYZ0001 Test_b
XYZ0002 Test_a
XYZ0003 Test_c
Any advice or suggestions is appreciated. Thanks in advance!

Sure start by looking at the link in my signature about splitting strings.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

lnardozi 61862 SSCommitted Points: 1931 More actions · Answer 2

Moden's string splitter (or any string splitter) can be used in this manner:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION TVF_2col

(

@col varchar(8000)

)

RETURNS TABLE

AS

RETURN

(

select a.item ky,b.item val from

[dbo].[DelimitedSplit8K](@col,'|') a

inner join

[dbo].[DelimitedSplit8K](@col,'|') b on b.itemnumber = a.itemnumber +1 and a.itemnumber % 2 =1

)

GO

Dwain Camps SSC Guru Points: 86948 More actions · Answer 3

There's no reason to call DelimitedSplit8K twice, even if it is super-efficient (bless its heart):

WITH SampleData (s) AS

(

)

SELECT s

,Col1=MAX(CASE WHEN ItemNumber%2 = 1 THEN Item END)

,Col2=MAX(CASE WHEN ItemNumber%2 = 0 THEN Item END)

FROM SampleData

CROSS APPLY dbo.DelimitedSplit8K(s, '|')

GROUP BY s, (1+ItemNumber)/2;

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

lnardozi 61862 SSCommitted Points: 1931 More actions · Answer 4

Dwain,

I bow before the majesty of your CASE / GROUP BY logic. I couldn't figure out how to do it without a temp table.

As my neighbor would say, "that's slicker 'n' own snot!"

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

dwain.c (3/6/2014)
There's no reason to call DelimitedSplit8K twice, even if it is super-efficient (bless its heart):
WITH SampleData (s) AS
(
SELECT 'XYZ00001|Test_b|XYZ00002|Test_a|XYZ00003|Test_c'
)
SELECT s
,Col1=MAX(CASE WHEN ItemNumber%2 = 1 THEN Item END)
,Col2=MAX(CASE WHEN ItemNumber%2 = 0 THEN Item END)
FROM SampleData
CROSS APPLY dbo.DelimitedSplit8K(s, '|')
GROUP BY s, (1+ItemNumber)/2;

+1000 Excellent.

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