这是我的字符串/列值:
ReferenceId=678&MobileNo=226789&Pnumber=9878&SIB=121314&.........&PIN=34&....
有这么多领域。我对上述领域感兴趣。如何在SQL Server 2012中分离/获取它们的值?2012年不支持string_split()
功能。
创建@Dave建议的UFN_STRING_SPLIT
函数链接
create FUNCTION [dbo].[UFN_STRING_SPLIT]
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
SELECT Item = y.i.value('(./text())[1]', 'nvarchar(max)')
FROM
(
SELECT x = CONVERT(XML, '<i>'
+ REPLACE(@List, @Delimiter, '</i><i>')
+ '</i>').query('.')
) AS a
CROSS APPLY x.nodes('i') AS y(i)
);
测试你的代码:
select
Left(Item, charindex('=', Item, 0) - 1) as FielName,
right(Item, len(Item) - charindex('=', Item, 0)) as [Value]
from
dbo.UFN_STRING_SPLIT('ReferenceId=678&MobileNo=226789&Pnumber=9878&SIB=121314', '&')