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