SqlServer Json解析 函数

自己写的，相目中用到的取某一JSON字段中，某个字字段的值。
SELECT tt.Pat_ID,tt.a,tt.b,tt.a-tt.b AS execCount FROM (
SELECT Pat_ID,
CONVERT(INT,SUBSTRING(
SUBSTRING(
Step_4_Infor,
CHARINDEX(‘Fractions_Tx":’, Step_4_Infor) + 15,
CHARINDEX(’",“ProgressDetail’, Step_4_Infor)
- CHARINDEX('Fractions_Tx”:’, Step_4_Infor) - 15
),
CHARINDEX(’/’,
SUBSTRING(
Step_4_Infor,
CHARINDEX(‘Fractions_Tx":’, Step_4_Infor) + 15,
CHARINDEX(’",“ProgressDetail’, Step_4_Infor)
- CHARINDEX('Fractions_Tx”:’, Step_4_Infor) - 15
)
)+1,LEN(SUBSTRING(
Step_4_Infor,
CHARINDEX(‘Fractions_Tx":’, Step_4_Infor) + 15,
CHARINDEX(’",“ProgressDetail’, Step_4_Infor)
- CHARINDEX('Fractions_Tx”:’, Step_4_Infor) - 15
))

                    ) ) AS a,

          CONVERT(INT, SUBSTRING(
                        SUBSTRING(
                                     Step_4_Infor,
                                     CHARINDEX('Fractions_Tx":', Step_4_Infor) + 15,
                                     CHARINDEX('","ProgressDetail', Step_4_Infor)
                                     - CHARINDEX('Fractions_Tx":', Step_4_Infor) - 15
                                 ),
                        0,
                        CHARINDEX(
                                     '/',
                                     SUBSTRING(
                                                  Step_4_Infor,
                                                  CHARINDEX('Fractions_Tx":', Step_4_Infor) + 15,
                                                  CHARINDEX('","ProgressDetail', Step_4_Infor)
                                                  - CHARINDEX('Fractions_Tx":', Step_4_Infor) - 15
                                              )
                                 )
                    )) AS b

FROM dbo.TreatmentProgress
WHERE PartStatus_Code = 1200 ) tt

–以下是找到的相关函数

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, ‘}’, ‘’);
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 * FROM [dbo].[TreatmentProgress]
–使用
SELECT dbo.fn_parsejson(Step_4_Infor,‘Fractions_Tx’) FROM [dbo].[TreatmentProgress] WHERE PartStatus_Code=1200