using charindex
-
Hi everyone i want split the data this below format..
1.Split the RR ("PL:TW RR:1.8%,PL:TX RR:0.9%") into LowendRR ("PL:TW RR:1.8%") and HighendRR ("PL:TX RR:0.9%")
2.Split the LowendRR ("PL:TW RR:1.8%") into LowendPL ("TW"), LowendPLValues ("1.8%")
3.Split the HighendRR ("PL:TX RR:0.9%") into HighendPL ("TX"), HighendPLValues ("0.9%")
using charindex,substrings am waiting for reply..:-)
-
You should create a function but I just copy pasted to work out each one
DECLARE @RR nvarchar(256), @LowendRR nvarchar(256)
, @HighendRR nvarchar(256)
, @LowendPL nvarchar(256)
, @HighendPL nvarchar(256)
, @LowendPLvalues nvarchar(256)
, @HighendPLvalues nvarchar(256)
, @SplitChar nchar(1)
SET @SplitChar = ','
SET @RR = 'PL:TW RR:1.8%,PL:TX RR:0.9%'
SELECT @LowendRR = substring(@RR,1,charindex(@SplitChar,@RR,0) - 1)
, @HighendRR = substring(@RR,charindex(@SplitChar,@RR,0) + 1, len(@RR) - charindex(@SplitChar,@RR,0))
SELECT @LowendRR LowendRR, @HighendRR HighendRR
/*First split on space*/
SET @SplitChar = ' '
SELECT @LowendPL = substring(@LowendRR,1,charindex(@SplitChar,@LowendRR,0) - 1)
, @HighendPL = substring(@HighendRR,1,charindex(@SplitChar,@HighendRR,0) - 1)
, @LowendPLvalues = substring(@LowendRR, charindex(@SplitChar,@LowendRR ,0) + 1, len(@LowendRR) - charindex(@SplitChar,@LowendRR ,0))
, @HighendPLvalues = substring(@HighendRR,charindex(@SplitChar,@HighendRR,0) + 1, len(@HighendRR) - charindex(@SplitChar,@HighendRR,0))
--SELECT @LowendPL LowendPL, @LowendPLvalues LowendPLvalues, @HighendPL HighendPL, @HighendPLvalues HighendPLvalues
/*2nd split on :*/
SET @SplitChar = ':'
SELECT @LowendPL = substring(@LowendPL, charindex(@SplitChar,@LowendPL ,0) + 1, len(@LowendPL) - charindex(@SplitChar,@LowendPL ,0))
, @HighendPL = substring(@HighendPL,charindex(@SplitChar,@HighendPL,0) + 1, len(@HighendPL) - charindex(@SplitChar,@HighendPL,0))
, @LowendPLvalues = substring(@LowendPLvalues, charindex(@SplitChar,@LowendPLvalues ,0) + 1, len(@LowendPLvalues) - charindex(@SplitChar,@LowendPLvalues ,0))
, @HighendPLvalues = substring(@HighendPLvalues,charindex(@SplitChar,@HighendPLvalues,0) + 1, len(@HighendPLvalues) - charindex(@SplitChar,@HighendPLvalues,0))
/*Final values*/
SELECT @LowendPL LowendPL, @LowendPLvalues LowendPLvalues, @HighendPL HighendPL, @HighendPLvalues HighendPLvalues
-
WITH SampleData (RR) AS
(
SELECT 'PL:TW RR:1.8%,PL:TX RR:0.9%'
)
SELECT LowendRR, HighendRR
,LowendPL = RIGHT(ls, CHARINDEX(':', REVERSE(ls))-1)
,LowendPLValues = RIGHT(LowendRR, CHARINDEX(':', REVERSE(LowendRR))-1)
,HighendPL = RIGHT(rs, CHARINDEX(':', REVERSE(rs))-1)
,HighendPLValues = RIGHT(HighendRR, CHARINDEX(':', REVERSE(HighendRR))-1)
FROM SampleData a
CROSS APPLY (SELECT cp = CHARINDEX(',', RR)) b
CROSS APPLY
(
SELECT LowendRR = LEFT(RR, cp-1), HighendRR=SUBSTRING(RR, cp+1, 99)
) c
CROSS APPLY
(
SELECT ls = LEFT(LowendRR, CHARINDEX(' ', LowendRR)-1)
,rs = LEFT(HighendRR, CHARINDEX(' ', HighendRR)-1)
) d;
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 -
Quick solution using the DelimitedSplit8K[/url] function
😎
USE tempdb;GO
SET NOCOUNT ON;
DECLARE @SAMPLE_DATA TABLE
(
SD_TEXT VARCHAR(100) NOT NULL
);
INSERT INTO @SAMPLE_DATA (SD_TEXT)
VALUES ('PL:TW RR:1.8%,PL:TX RR:0.9%')
;
;WITH FIRST_SPLIT AS
(
SELECT
C.ItemNumber
,C.Item
FROM @SAMPLE_DATA SD
OUTER APPLY dbo.DelimitedSplit8K(SD.SD_TEXT,CHAR(44)) AS C
)
,SECOND_SPLIT AS
(
SELECT
FS.ItemNumber AS PARENT_ID
,S.ItemNumber AS ITEM_ID
,S.Item AS ITEM
FROM FIRST_SPLIT FS
OUTER APPLY dbo.DelimitedSplit8K(FS.Item,CHAR(32)) AS S
)
SELECT
SP.PARENT_ID
,SP.ITEM_ID
,X.ItemNumber AS VALUE_ID
,X.Item AS VALUE
FROM SECOND_SPLIT SP
OUTER APPLY dbo.DelimitedSplit8K(SP.ITEM,CHAR(58)) AS X
;
Results
PARENT_ID ITEM_ID VALUE_ID VALUE---------- -------- --------- ------
1 1 1 PL
1 1 2 TW
2 1 1 PL
2 1 2 TX
2 2 1 RR
2 2 2 0.9%
1 2 1 RR
1 2 2 1.8%
-
Your string can be turned into valid XML with three REPLACE functions, then you can use XML functions to do all the parsing. Assuming none of the possible RR values might contain characters like ", <, >, or &.
DECLARE @input VARCHAR(100) = 'PL:TW RR:1.8%,PL:TX RR:0.9%';DECLARE @PL XML = '<PL>' + REPLACE(REPLACE(REPLACE(@input + ',', 'PL:', '<'), ':', '="'), ',', '" />') + '</PL>';
SELECT @PL;
-- Get all values as a table
SELECT param = v.value('local-name(.)', 'varchar(50)'),
value = v.value('./@RR', 'varchar(50)')
FROM @PL.nodes('/PL/*') p(v)
-- Unpivot that table to get individual values in one row
-- Assuming TW and TX are not the only possible names
SELECT TW = MAX(CASE param WHEN 'TW' THEN value END),
TX = MAX(CASE param WHEN 'TX' THEN value END),
Fred = MAX(CASE param WHEN 'AA' THEN value END),
Barney = MAX(CASE param WHEN 'ZZ' THEN value END)
FROM (
SELECT param = v.value('local-name(.)', 'varchar(50)'),
value = v.value('./@RR', 'varchar(50)')
FROM @PL.nodes('/PL/*') p(v)
) PL
-
Scott Coleman (1/22/2015)
Your string can be turned into valid XML with three REPLACE functions, then you can use XML functions to do all the parsing. Assuming none of the possible RR values might contain characters like ", <, >, or &.DECLARE @input VARCHAR(100) = 'PL:TW RR:1.8%,PL:TX RR:0.9%';DECLARE @PL XML = '<PL>' + REPLACE(REPLACE(REPLACE(@input + ',', 'PL:', '<'), ':', '="'), ',', '" />') + '</PL>';
SELECT @PL;
-- Get all values as a table
SELECT param = v.value('local-name(.)', 'varchar(50)'),
value = v.value('./@RR', 'varchar(50)')
FROM @PL.nodes('/PL/*') p(v)
-- Unpivot that table to get individual values in one row
-- Assuming TW and TX are not the only possible names
SELECT TW = MAX(CASE param WHEN 'TW' THEN value END),
TX = MAX(CASE param WHEN 'TX' THEN value END),
Fred = MAX(CASE param WHEN 'AA' THEN value END),
Barney = MAX(CASE param WHEN 'ZZ' THEN value END)
FROM (
SELECT param = v.value('local-name(.)', 'varchar(50)'),
value = v.value('./@RR', 'varchar(50)')
FROM @PL.nodes('/PL/*') p(v)
) PL
Oh, careful, Scott. The concatenation will absolutely crush performance here. It takes turns beating and losing to a While loop. Here's a picture of some massive testing previously done...
Here's the article that picture came from.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
--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) -
With you around here Jeff, I figured it wasn't up to me to post another article comparing all possible string splitting techniques. I've enjoyed your articles on string splitting, and most of my servers are already infested with tally tables. Just before reading your response I actually gave some tally table code to a colleague with a new server who needed to split strings.
But this seemed to be a question about a very constrained range of short strings. The XML solution popped into my head so I posted it without a detailed performance analysis. I think there is some value in having XML queries among your bag of tricks, but it is very true that you need to be wary of the overhead involved.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply