Query to Split the String as rows and columns in SQL

Question

Post reply

Query to Split the String as rows and columns in SQL

g_rose42

SSC Enthusiast

Points: 134
More actions
October 19, 2015 at 1:59 am

#301513

I have a string that contains series of parameters with separators.i need to split the parameters and its values as rows and columns.
e.g string = "Param1=3;param2=4,param4=testval;param6=11;..etc" here the paramerter can be anything and in any number not fixed parameters.
Currently am using the below function and getting the parameters by each in select statement as mentioned below.
select [dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param1=',';') as param1,
[dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param2=',';') as param2
CREATE FUNCTION [dbo].[rvlf_fn_GetParamValueWithIndex]
(
@CustomProp varchar(max),
@StringName Varchar(50),
@Separator char(1)
)
RETURNS varchar(max)
AS
BEGIN
IF(LEN(@CustomProp)> 0 AND RIGHT(@CustomProp, 1) <> ';')
BEGIN
SET @CustomProp = @CustomProp+';'
END
DECLARE @vcduration varchar(max)
if(@Separator is null)
begin
Set @vcduration = case when CHARINDEX(@StringName,@CustomProp,1)!=0 then
SUBSTRING(@CustomProp,CHARINDEX(@StringName,@CustomProp,1)+len(@StringName),((CHARINDEX(@StringName,@CustomProp,1)+len(@StringName)))) else null end
end
else
begin
Set @vcduration = case when CHARINDEX(@StringName,@CustomProp,1)!=0 then
SUBSTRING(@CustomProp,CHARINDEX(@StringName,@CustomProp,1)+len(@StringName),(CHARINDEX(@Separator,@CustomProp,CHARINDEX(@StringName,@CustomProp,1))-(CHARINDEX(@StringName,@CustomProp,1)+len(@StringName)))) else null end
end
RETURN @vcduration
END
I need any alternate solution for the above. For the Output as below,
-----------------------------------
Param1 param2 param4 param6
----------------------------------
3 4 testval 11
--------------------------------

Viewing 9 posts - 1 through 9 (of 9 total)

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

Ant-Green SSC Guru Points: 113566 More actions · Answer 1

Sounds like you need a double split dynamic pivot

You will need the dbo.delimitedsplit8k function which can be found in my signature on the "String Splitter Link"

Then you will need to do something like this

--Create temp holding of the first split by ;

select * into #temp1 from dbo.delimitedsplit8k ('Param1=3;param2=4;param4=testval;param6=11',';')

--Create temp holiding of the second split by =

select t1.ItemNumber as t1ItemNumber, t1.item as t1item, spl.ItemNumber, spl.item into #temp2 from #temp1 t1 cross apply dbo.delimitedsplit8k (t1.item,'=') spl

--Dynamic Pivot

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

SET @columns = N'';

SELECT @columns += N', p.' + QUOTENAME(item)

FROM (SELECT item FROM #temp2 WHERE itemnumber = 1) AS x; --Second Split Output, value 1 for everything before the =

SET @sql = N'

SELECT ' + STUFF(@columns, 1, 2, '') + '

FROM

(

select

t1.item,

t2.item as itemvalue

from

(selectitem, t1itemnumber from #temp2 where itemnumber = 1) as t1

join

(select item, t1itemnumber from #temp2 where itemnumber = 2) as t2

on t1.t1itemnumber = t2.t1itemnumber

) AS j

PIVOT

(

MAX(itemvalue) FOR item IN ('

+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')

+ ')

) AS p;';

PRINT @sql;

EXEC sp_executesql @sql;

drop table #temp1, #temp2;

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

This can be done without temp tables as there's a single row returned.

Unless I'm missing something, here's my suggestion.

--Dynamic Pivot

DECLARE @sql NVARCHAR(MAX);

/*

STUFF is used to change the first tab and comma to SELECT

QUOTENAME is used to handle string values

CROSS APPLY is used to split values and names of the parameters

*/

SET @sql = STUFF((SELECT CHAR(9) + ',' + Name + QUOTENAME( Value, '''') + CHAR( 13)

FROM dbo.delimitedsplit8k ('Param1=3;param2=4;param4=testval;param6=11',';') t

CROSS APPLY (SELECT LEFT( Item, CHARINDEX( '=', Item + '=')),

STUFF( Item, 1, CHARINDEX( '=', Item), '')) ca( Name, Value)

ORDER BY ItemNumber

FOR XML PATH(''), TYPE).value( '.', 'NVARCHAR(MAX)'), 1,2, N'SELECT ')

PRINT @sql;

EXEC sp_executesql @sql;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Ant-Green SSC Guru Points: 113566 More actions · Answer 3

Luis Cazares (10/19/2015)
This can be done without temp tables as there's a single row returned.
Unless I'm missing something, here's my suggestion.
--Dynamic Pivot
DECLARE @sql NVARCHAR(MAX);
/*
STUFF is used to change the first tab and comma to SELECT
QUOTENAME is used to handle string values
CROSS APPLY is used to split values and names of the parameters
*/
SET @sql = STUFF((SELECT CHAR(9) + ',' + Name + QUOTENAME( Value, '''') + CHAR( 13)
FROM dbo.delimitedsplit8k ('Param1=3;param2=4;param4=testval;param6=11',';') t
CROSS APPLY (SELECT LEFT( Item, CHARINDEX( '=', Item + '=')),
STUFF( Item, 1, CHARINDEX( '=', Item), '')) ca( Name, Value)
ORDER BY ItemNumber
FOR XML PATH(''), TYPE).value( '.', 'NVARCHAR(MAX)'), 1,2, N'SELECT ')
PRINT @sql;
EXEC sp_executesql @sql;

A lot nicer than mine.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

DECLARE @StartTime DATETIME

DECLARE @Item VARCHAR(20) = ''

SET @StartTime = GETDATE()

SELECT @Item = @Item + ','+d2.Item

FROM dbo.DelimitedSplit8K_LEAD ('Param1=3;param2=4;param4=testval;param6=11',';') d1

CROSS APPLY dbo.DelimitedSplit8K_LEAD (d1.Item, '=') d2

WHERE d2.ItemNumber = 2

ORDER BY d1.ItemNumber

SELECT DATEDIFF(MILLISECOND, @StartTime, GETDATE())

SELECT STUFF(@Item,1,1,'')

SET @StartTime = GETDATE()

SELECT

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

'Param1=3;param2=4;param4=testval;param6=11',

'Param',''),'1=',''),'2=',''),'3=',''),'4=',''),'5=',''),'6=',''),'7=',''),'8=',''),'9=',''),'10=',''),';',',')

SELECT DATEDIFF(MILLISECOND, @StartTime, GETDATE())

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

Luis Cazares (10/19/2015)
This can be done without temp tables as there's a single row returned.
Unless I'm missing something, here's my suggestion.
--Dynamic Pivot
DECLARE @sql NVARCHAR(MAX);
/*
STUFF is used to change the first tab and comma to SELECT
QUOTENAME is used to handle string values
CROSS APPLY is used to split values and names of the parameters
*/
SET @sql = STUFF((SELECT CHAR(9) + ',' + Name + QUOTENAME( Value, '''') + CHAR( 13)
FROM dbo.delimitedsplit8k ('Param1=3;param2=4;param4=testval;param6=11',';') t
CROSS APPLY (SELECT LEFT( Item, CHARINDEX( '=', Item + '=')),
STUFF( Item, 1, CHARINDEX( '=', Item), '')) ca( Name, Value)
ORDER BY ItemNumber
FOR XML PATH(''), TYPE).value( '.', 'NVARCHAR(MAX)'), 1,2, N'SELECT ')
PRINT @sql;
EXEC sp_executesql @sql;

Careful now... Do you know what the maximum length of each parameter will be? Me neither. But I can tell you that the maximum length allowed for QUOTENAME is only 128 characters. Any more than that and QUOTENAME returns a NULL. And, yes... because this is concatenated dynamic SQL, you also need to delouse each parameter name for SQL Injection, as well.

--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 SSC Guru Points: 1004650 More actions · Answer 6

ChrisM@Work (10/19/2015)
DECLARE @StartTime DATETIME DECLARE @Item VARCHAR(20) = '' SET @StartTime = GETDATE() SELECT @Item = @Item + ','+d2.Item FROM dbo.DelimitedSplit8K_LEAD ('Param1=3;param2=4;param4=testval;param6=11',';') d1 CROSS APPLY dbo.DelimitedSplit8K_LEAD (d1.Item, '=') d2 WHERE d2.ItemNumber = 2 ORDER BY d1.ItemNumber SELECT DATEDIFF(MILLISECOND, @StartTime, GETDATE()) SELECT STUFF(@Item,1,1,'') SET @StartTime = GETDATE() SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( 'Param1=3;param2=4;param4=testval;param6=11', 'Param',''),'1=',''),'2=',''),'3=',''),'4=',''),'5=',''),'6=',''),'7=',''),'8=',''),'9=',''),'10=',''),';',',') SELECT DATEDIFF(MILLISECOND, @StartTime, GETDATE())

It could be even shorter and more flexible and less limited than that but then there's that nasty SQL Injection thing to worry about. I'm not sure that replacing ";" will make it injection proof.

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Jeff Moden (10/24/2015)
Careful now... Do you know what the maximum length of each parameter will be? Me neither. But I can tell you that the maximum length allowed for QUOTENAME is only 128 characters. Any more than that and QUOTENAME returns a NULL. And, yes... because this is concatenated dynamic SQL, you also need to delouse each parameter name for SQL Injection, as well.

Thank you for the clarification. I've never experienced that problem (and I don't expect to experience it) but it's good to keep it in mind. On the other hand, I really expect that the parameter names are not generated by user input. I'm probably not as paranoid as a true DBA :-D.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004650 More actions · Answer 8

Luis Cazares (10/24/2015)
Jeff Moden (10/24/2015)
Careful now... Do you know what the maximum length of each parameter will be? Me neither. But I can tell you that the maximum length allowed for QUOTENAME is only 128 characters. Any more than that and QUOTENAME returns a NULL. And, yes... because this is concatenated dynamic SQL, you also need to delouse each parameter name for SQL Injection, as well.
Thank you for the clarification. I've never experienced that problem (and I don't expect to experience it) but it's good to keep it in mind. On the other hand, I really expect that the parameter names are not generated by user input. I'm probably not as paranoid as a true DBA :-D.

I'm right there with you on thinking that there's likely no human generating the parameter names but I am an old dude that's seen too much in my life. 😛 It's sometimes amazing what a ticked off Developer will do before leaving a company or what a user in trouble with money will do.

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