求一sql函数写法 现有一个字符串str如下:["A":12,"B":3,"C":1,"D":40]怎样用一个函数把str的内容分解并依次赋值到下面的4个变量中@p_A, @p_B, @p_C, @p_D int; 求精简的,谢谢! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 DECLARE @str NVARCHAR(1000)SET @str = '["A":12,"B":3,"C":1,"D":40]'DECLARE @p_A int, @p_B INT, @p_C int, @p_D int; SELECT @p_A = A, @p_B = B, @p_C = C, @p_D = DFROM( SELECT fieldname = LEFT(b.NAME, CHARINDEX(':',b.name)-1), fieldvalue = RIGHT(b.NAME,LEN(b.name)-CHARINDEX(':',b.name)) FROM (SELECT [name]=CONVERT(XML, '<root><v>'+replace(REPLACE(REPLACE(REPLACE(@str, '"',''), '[', ''),']',''),',','</v><v>')+'</v></root>')) a OUTER APPLY (SELECT [name] = C.v.value('.','NVARCHAR(MAX)') FROM a.[name].nodes('/root/v') C(v)) b) mPIVOT(MAX(fieldvalue) FOR fieldname IN([A],[B],[c],[d])) nSELECT @p_A,@p_b,@p_c,@p_d/*(无列名) (无列名) (无列名) (无列名)12 3 1 40*/ --你这明显是JSON格式,为什么不放在前台?--或者用循环的方式来截取字段值,并分别赋给变量。--SQL SERVER处理字符串的能力还是比较弱的。 如果str的格式是这样的话呢?字符串str如下: ["Aasd":12,"Bifg":3,"Csd":1,"Ddd":40]再难一点,包含若干子串,字符串str如下: {["Aasd":12,"Bifg":3,"Csd":1,"Ddd":40],["Aasd":16,"Bifg":5,"Csd":0,"Ddd":49]} --仅供参考DECLARE @str NVARCHAR(1000)SET @str = '{["Aasd":12,"Bifg":3,"Csd":1,"Ddd":40],["Aasd":16,"Bifg":5,"Csd":0,"Ddd":49]}'SET @str = REPLACE(SUBSTRING(@str,3,LEN(@str)-4), '"','')SELECT rowid, fieldname = LEFT(c.NAME, CHARINDEX(':',c.name)-1), fieldvalue = RIGHT(c.NAME,LEN(c.name)-CHARINDEX(':',c.name))FROM( SELECT rowid = ROW_NUMBER() OVER(ORDER BY GETDATE()), name=CONVERT(XML, '<root><v>'+replace(b.name,',','</v><v>')+'</v></root>') FROM (SELECT [name]=CONVERT(XML, '<root><v>'+replace(@str,'],[','</v><v>')+'</v></root>')) a OUTER APPLY (SELECT [name] = C.v.value('.','NVARCHAR(MAX)') FROM a.[name].nodes('/root/v') C(v)) b) tCROSS APPLY( SELECT [name] = C.v.value('.','NVARCHAR(MAX)') FROM t.[name].nodes('/root/v') C(v)) c/*rowid fieldname fieldvalue1 Aasd 121 Bifg 31 Csd 11 Ddd 402 Aasd 162 Bifg 52 Csd 02 Ddd 49*/ 请问如何循环日期执行存储过程? sql查询问题 一个物理表的建立 怎样取当前排名?谢谢 局域网内客户端A访问服务器S,一定要用Named Pipes 协议,我用TCP/IP协议,没用Named Pipes 会提示下面的出错? 简单问题,不解? 碰到个难题, 头大了, 各位来帮个忙, 问一个简单的触发器的问题(好久没搞都忘记了) SqlServer2000 安装问题,请高手解决。 为什么这个循环只出双数??? sql server setup已停止工作 在应用程序中如何引用SQL内的存储过程或DLL中函数?
SET @str = '["A":12,"B":3,"C":1,"D":40]'DECLARE @p_A int, @p_B INT, @p_C int, @p_D int; SELECT
@p_A = A,
@p_B = B,
@p_C = C,
@p_D = D
FROM
(
SELECT
fieldname = LEFT(b.NAME, CHARINDEX(':',b.name)-1),
fieldvalue = RIGHT(b.NAME,LEN(b.name)-CHARINDEX(':',b.name))
FROM
(SELECT [name]=CONVERT(XML, '<root><v>'+replace(REPLACE(REPLACE(REPLACE(@str, '"',''), '[', ''),']',''),',','</v><v>')+'</v></root>')) a
OUTER APPLY
(SELECT [name] = C.v.value('.','NVARCHAR(MAX)') FROM a.[name].nodes('/root/v') C(v)) b
) m
PIVOT
(MAX(fieldvalue) FOR fieldname IN([A],[B],[c],[d])) nSELECT @p_A,@p_b,@p_c,@p_d
/*
(无列名) (无列名) (无列名) (无列名)
12 3 1 40
*/
--或者用循环的方式来截取字段值,并分别赋给变量。
--SQL SERVER处理字符串的能力还是比较弱的。
如果str的格式是这样的话呢?
字符串str如下:
["Aasd":12,"Bifg":3,"Csd":1,"Ddd":40]再难一点,包含若干子串,字符串str如下:
{["Aasd":12,"Bifg":3,"Csd":1,"Ddd":40],["Aasd":16,"Bifg":5,"Csd":0,"Ddd":49]}
DECLARE @str NVARCHAR(1000)
SET @str = '{["Aasd":12,"Bifg":3,"Csd":1,"Ddd":40],["Aasd":16,"Bifg":5,"Csd":0,"Ddd":49]}'
SET @str = REPLACE(SUBSTRING(@str,3,LEN(@str)-4), '"','')SELECT
rowid,
fieldname = LEFT(c.NAME, CHARINDEX(':',c.name)-1),
fieldvalue = RIGHT(c.NAME,LEN(c.name)-CHARINDEX(':',c.name))
FROM
(
SELECT rowid = ROW_NUMBER() OVER(ORDER BY GETDATE()), name=CONVERT(XML, '<root><v>'+replace(b.name,',','</v><v>')+'</v></root>') FROM
(SELECT [name]=CONVERT(XML, '<root><v>'+replace(@str,'],[','</v><v>')+'</v></root>')) a
OUTER APPLY
(SELECT [name] = C.v.value('.','NVARCHAR(MAX)') FROM a.[name].nodes('/root/v') C(v)) b
) t
CROSS APPLY
(
SELECT [name] = C.v.value('.','NVARCHAR(MAX)') FROM t.[name].nodes('/root/v') C(v)
) c/*
rowid fieldname fieldvalue
1 Aasd 12
1 Bifg 3
1 Csd 1
1 Ddd 40
2 Aasd 16
2 Bifg 5
2 Csd 0
2 Ddd 49
*/