Stored procedure to parse a string

问题:

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.

回答1:

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)

回答2:

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

回答3:

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.

回答4:

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