2 Replies - 850 Views - Last Post: 03 November 2015 - 08:08 AM

#1 G_rose42   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 60
  • Joined: 15-December 10

Query to Split the String as rows and columns in SQL

Posted 19 October 2015 - 12:41 AM

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

Is This A Good Question/Topic? 0
  • +

Replies To: Query to Split the String as rows and columns in SQL

#2 BBeck   User is offline

  • Here to help.
  • member icon


Reputation: 792
  • View blog
  • Posts: 1,886
  • Joined: 24-April 12

Re: Query to Split the String as rows and columns in SQL

Posted 03 November 2015 - 07:52 AM

This appears to have CLR written all over it. The first step, in my mind, would be to tell whoever's sending you crazy data to stop sending it until they learn how to normalize their data. Nothing beats not having to deal with un-normalized data like this.

But I know in the real world you can't always do that as I've been forced to deal with data even crazier than this. And when I faced a situation like this, I turned to CLR, but I've been programming in C# for quite awhile now outside of SQL Server and am very comfortable with it.

And that appears to be the favorite solution of others. This website discusses string parsing in quite a bit of detail with multiple solutions. Since they let you specify a delimiter, you could use their solution to run the string through twice, once with ';' and then again for '=' and end up with a table of parameter names and matching values. It's not the form you wanted, but it's probably the first step to get to the form of output you want.

First, you have to parse out all the sub-strings between the semi-colon delimiters. Then you need to split the two values on either side of the equals delimiter. You should be able to output this as a two column table of parameter names and their values.

You're probably going to have to do that if you can skip parameters like you skipped Param3. (If the parameter names are always in order and numerically contiguous, then you can ignore what's on the left side of the equals sign because they are serial and at most you just need to count how many there are. Under these conditions you could calculate the parameter names when needed. If they are random then you can't.) Then if you want those parameter names to be column names of a table, you're probably going to have to build a table from the table of parameter names. You could probably do that with Dynamic SQL if you were not solving this whole problem with CLR.

SQL Server is designed to deal with normalized data. De-normalized data just causes problems but CLR can deal with anything.

This post has been edited by BBeck: 03 November 2015 - 07:57 AM

Was This Post Helpful? 0
  • +
  • -

#3 BBeck   User is offline

  • Here to help.
  • member icon


Reputation: 792
  • View blog
  • Posts: 1,886
  • Joined: 24-April 12

Re: Query to Split the String as rows and columns in SQL

Posted 03 November 2015 - 08:08 AM

And when I ran your code I got:
param1     param2
---------  -------------
3          4,param4=testval

(1 row(s) affected)


This post has been edited by BBeck: 03 November 2015 - 08:08 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1