解析字符串的存储过程 [英] 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

在存储过程中，我已经声明了像 @x、@y、@z、@t 这样的变量来获取值作为

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

一旦我将值解析为 @x、@y、@z、@t 等，我就必须在存储过程中使用这些值.

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

请告诉我如何解析输入字符串以获取Name、City、Mobile、Role 的值到@x、@y、@z 和@t 分别.

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.

推荐答案

一种可能的解决方案是使用 XML

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)

这篇关于解析字符串的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！