Stored procedure to parse a string

Go To StackoverFlow.com

1

I need to write a stored procedure for which the input is a string.

The input string contains variable names and their values separated by pipeline delimiter like this:

Name =Praveen | City=Hyderabad | Mobile=48629387429| Role=User| etc

In the stored procedure I have declared variables like @x, @y, @z, @t to obtain values as

@x=Praveen (Name value)
@y=Hyderabad (City Value)
@z=48629387429(Mobile Value)
@t=User(Role Value)

Also input string can have the values in any order like

City=Hyderabad | Mobile=48629387429 | Role=User | Name =Praveen |etc

Once I parse the values into @x, @y, @z, @t etc I have to use these values in the stored procedure.

Kindly let me how I can parse the input string to obtain the values of Name, City, Mobile, Role into @x, @y, @z and @t respectively.

2012-04-05 18:52
by user1316031
It would be a lot easier with 4 parameters instead of - Soader03 2012-04-05 18:56
I have tried to parse the string using functions like CHARINDEX,SUBSTRING but couldn't able to figure out the logic - user1316031 2012-04-05 19:42


4

One possible solution is use XML

DECLARE @text VARCHAR(1000) 
        ,@xml xml

SELECT @text = 'City=Hyderabad | Mobile=48629387429 | Role=User | Name =Praveen'

SELECT @text = REPLACE(@text,'|','"')
    ,@text = REPLACE(@text,'=','="')
    ,@text = '<row ' + @text + '"/>'

SELECT @xml = CAST(@text AS XML)

select 
    line.col.value('@Name[1]', 'varchar(100)') AS Name
    ,line.col.value('@City[1]', 'varchar(100)') AS City
    ,line.col.value('@Mobile[1]', 'varchar(100)') AS Mobile 
    ,line.col.value('@Role[1]', 'varchar(100)') AS Role 
FROM @xml.nodes('/row') AS line(col)
2012-04-05 20:01
by EricZ
+1. XML is one of the cleaner ways to pass a string of key/value pairs to SQL Server - Tim Lehner 2012-04-05 20:14
Why does everyone jump to converting to XML in a relational database that happens to support XML - Michael Rice 2012-04-05 20:25
To me, it's a fairly straightforward solution for something that, ideally, your relational database shouldn't be forced to do - Tim Lehner 2012-04-05 20:27
@EricZ: Does this solution have considerations for memory consuming ? Do you recommend using it for large datasets? tn - Emilio Borraz 2015-09-16 16:42


0

Let's assume your input param is called @Text.

DECLARE @Text varchar(255),
    @x varchar(255)

SET @Text = 'Name=Praveen | City=Hyderabad | Mobile=48629387429| Role=User'

-- Added to show how to account for non-trailing |
SET @Text = @Text + ' | ';

SET @x = LTRIM(RTRIM(substring(
         @Text,
         charindex('Name=', @Text) + LEN('Name='),
         charindex(' | ', @Text, charindex('Name=', @Text)) - LEN('Name=')
         )))

SELECT @x

Then just repeat this for @y, @z, @t change Name= to whatever your break is.

2012-04-05 19:09
by Michael Rice
Hi Michael Thanks for your solution In the input string the last parameter doesn't end with | Eg : 'Name=Praveen | City=Hyderabad | Mobile=48629387429 | Role=User ' In this case the code fails for the last parameter. As there is no | delimter for last variable in the string . How can we handle this - user1316031 2012-04-05 19:57
Updated, take a look - Michael Rice 2012-04-05 20:24


0

I definitely recommend doing your string parsing on the program side as opposed to the data side. That being said, if you absolutely must you can try doing something similar to this:

DECLARE @String [nvarchar](256) = 'Name=Praveen | City=Hyderabad | Mobile=48629387429 | Role=User |'

DECLARE @name [nvarchar](256) = (SELECT SUBSTRING(@String, CHARINDEX('Name=', @String)+5, CHARINDEX('|', @String)))

DECLARE @city [nvarchar](256) = (SELECT SUBSTRING(@String, CHARINDEX('City=', @String)+5, CHARINDEX('|', @String)))

DECLARE @mobile [nvarchar](256) = (SELECT SUBSTRING(@String, CHARINDEX('Mobile=', @String)+7, CHARINDEX('|', @String)))

DECLARE @role [nvarchar](256) = (SELECT SUBSTRING(@String, CHARINDEX('Role=', @String)+5, CHARINDEX('|', @String)))

SELECT RTRIM(LTRIM(LEFT(@name, CHARINDEX('|', @name)-1))) AS Name,
        RTRIM(LTRIM(LEFT(@city, CHARINDEX('|', @city)-1))) AS City,
        RTRIM(LTRIM(LEFT(@mobile, CHARINDEX('|', @mobile)-1))) AS Mobile,
        RTRIM(LTRIM(LEFT(@role, CHARINDEX('|', @role)-1))) AS Role

This returns:

 Name    | City      | Mobile      | Role
________________________________________________
 Praveen | Hyderabad | 48629387429 | User

Note that the length being addedfrom the CHARINDEX in the initial queries are equal to the search string.

"Name=" is equal to 5 characters so we add 5 to move the index past the = sign, "Mobile=" is equal to 7 so we add 7.

Similarly in the end SELECT query we are subtracting 1 from each CHARINDEX to remove the | symbol.

Sources:

SUBSTRING

CHARINDEX

LEFT

LTRIM

RTRIM

2012-04-05 19:20
by jon3laze
Hi jon3laze , Thank you so much for your solution ! In the input string the last parameter doesn't end with | Eg : 'Name=Praveen | City=Hyderabad | Mobile=48629387429 | Role=User ' In this case the code fails for the last parameter. As there is no | delimter for last variable in the string . How can we handle this - user1316031 2012-04-05 19:54


0

Here's a fun way using a loop for string manipulation. Note how we are defining our @x, @y, etc. variable to grab a particular value.

-- Simulate proc parameter
declare @input nvarchar(max) = 'Name =Praveen | City=Hyderabad | Mobile=48629387429| Role=User'

-- OP's preferred destination vars
declare @x nvarchar(max) = 'Name'
declare @y nvarchar(max) = 'City'
declare @z nvarchar(max) = 'Mobile'
declare @t nvarchar(max) = 'Role'

-- The key/value delimiters we are expecting
declare @recordDelim nchar(1) = '|'
declare @valueDelim nchar(1) = '='

-- Temp storage
declare @inputTable table (
      name nvarchar(128) not null primary key
    , value nvarchar(max) null
)

-- Get all key/value pairs
while ltrim(rtrim(@input)) != '' begin
    insert into @inputTable (name) select ltrim(rtrim(replace(left(@input, isnull(nullif(charindex(@recordDelim, @input), 0), len(@input))), @recordDelim, '')))
    select @input = ltrim(rtrim(right(@input, len(@input) - isnull(nullif(charindex(@recordDelim, @input), 0), len(@input)))))
end

-- Separate keys and values
update @inputTable
set name = ltrim(rtrim(left(name, isnull(nullif(charindex(@valueDelim, name) - 1, 0), len(name)))))
    , value = ltrim(rtrim(right(name, len(name) - isnull(nullif(charindex(@valueDelim, name), 0), len(name)))))

-- Populate the variables
-- If any are null, then this key/value wasn't present
set @x = (select value from @inputTable where name = @x)
set @y = (select value from @inputTable where name = @y)
set @z = (select value from @inputTable where name = @z)
set @t = (select value from @inputTable where name = @t)

Also, from the irregular spacing in your input, I'm guessing you want to trim everything coming in (which is why this proc does that all over the place).

2012-04-05 20:24
by Tim Lehner
Ads