SQLServer 解析JSON字符串

---------------------------------------------------
-----???????????解析字符串函数????????????????-----
---------------------------------------------------
CREATE?FUNCTION?[dbo].[fn_split](@p_str?VARCHAR(8000),?@p_split?VARCHAR(10))
RETURNS?@tab?TABLE(tid?VARCHAR(2000))
AS
BEGIN
DECLARE?@idx?INT
DECLARE?@len?INT
SELECT?@len?=?LEN(@p_split),?@idx?=?CHARINDEX(@p_split,?@p_str,?1)
WHILE(@idx?>=?1)
BEGIN
INSERT?INTO?@tab?SELECT?LEFT(@p_str,?@idx?-?1)
SELECT?@p_str?=?RIGHT(@p_str,?LEN(@p_str)?-?@idx?-?@len?+?1),?1)
END
if(@p_str?<>?'')?INSERT?INTO?@tab?SELECT?@p_str
RETURN
END;

-------------------------------
--??解析JSON字符串??--
-------------------------------
--p_jsonstr?json字符串
--p_key?键
--返回p_key对应的值
CREATE?FUNCTION?[dbo].[fn_parsejson](@p_jsonstr?VARCHAR(8000),?????????????????????????????????@p_key?VARCHAR(200))?
RETURNS?VARCHAR(3000)
AS??????
BEGIN
DECLARE?@rtnVal?VARCHAR(3000);
??DECLARE?@i?INT;
??DECLARE?@jsonkey?VARCHAR(200);
??DECLARE?@jsonvalue?VARCHAR(1000);
??DECLARE?@json?VARCHAR(8000);
??DECLARE?@tmprow?VARCHAR(2000);
??DECLARE?@tmpval?VARCHAR(2000);
??
??IF(@p_jsonstr?IS?NOT?NULL)
BEGIN
?????SET?@json?=?REPLACE(@p_jsonstr,?'{',?'');
?????SET?@json?=?REPLACE(@json,?'}',?'"',?'');
DECLARE?@json_cur?CURSOR;??--?声明外层游标
SET?@json_cur?=?CURSOR?FOR?SELECT?tid?FROM?fn_split(@json,?',');
OPEN?@json_cur--?打开游标（外层游标）
FETCH?NEXT?FROM?@json_cur?INTO?@tmprow--?提取外层游标行
WHILE(@@FETCH_STATUS?=?0)
BEGIN
IF(@tmprow?IS?NOT?NULL)
BEGIN
SET?@i?=?0;
SET?@jsonkey?=?'';
SET?@jsonvalue?=?'';
DECLARE?@str_cur?CURSOR;--?声明内层游标
SET?@str_cur?=?CURSOR?FOR?SELECT?tid?FROM?fn_split(@tmprow,?':');--第二次拆分后的游标（内层游标）
OPEN?@str_cur??--?打开游标
FETCH?NEXT?FROM?@str_cur?INTO?@tmpval--?提取内层游标行
WHILE(@@FETCH_STATUS?=?0)
BEGIN
IF(@i?=?0)?
BEGIN
SET?@jsonkey?=?@tmpval
END
IF(@i?=?1)
BEGIN
SET?@jsonvalue?=?@tmpval
END
SET?@i?=?@i?+?1
FETCH?NEXT?FROM?@str_cur?into?@tmpval--?内层游标下移一行
END
CLOSE?@str_cur--?关闭内层游标
DEALLOCATE?@str_cur?--?释放内层游标
IF(@jsonkey?=?@p_key)
BEGIN
SET?@rtnVal?=?@jsonvalue
END
END
FETCH?NEXT?FROM?@json_cur?INTO?@tmprow--?内层游标结束后，外层游标下移一行
END
CLOSE?@json_cur--?关闭外层游标
DEALLOCATE?@json_cur--?释放外层游标
END?
??
??RETURN?@rtnVal??
END

使用示例：

Select?dbo.fn_parsejson(
'{"billPrice":"1.67","buyDate":"2009-12-30","currentStatus":"有租约","decoration":"精装","empCode":"174999",","houseId":"F20BEBE259794C858C76122BCBACC71F","id":"36398","isAccompany":"0","isCollect":"1","isOnlyOne":"1","isShotProperty":"1","noCollectReason":"","parking":"0","propertyNo":"5885109","recordDate":"2017-06-08","recordRemark":"啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊"}','buyDate')

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!