SQL Server parsing JSON strings

--------------------------------------------------------             Parsing String Functions                  --------------------------------------------------------create function [dbo]. [Fn_split] (@p_str  varchar (8000),  @p_split  varchar (10)) returns  @tab  table (tid varchar) asbegindeclare  @idx  INTDECLARE  @len   intselect  @len  = len (@p_split),  @idx  = charindex (@p_split,  @p_str,  1) while (@idx  >= 1) begininsert into  @tab  select left (@p_str,  @idx  -  1) select  @p_str  = right (@p_str,  len (@p_str)  -  @idx  -  @len  +  1),  @idx  = charindex (@p_split,  @p_str,  1) ENDif (@p_str  <>  ")  INSERT INTO  @tab  SELECT  @p_strRETURNEND;

---------------------------------   Parse JSON strings   -----------------------------------p_jsonstr  json string--p_key  Key--Returns the p_key corresponding value create function [dbo]. [Fn_parsejson] (@p_jsonstr  varchar (8000),                                    @p_key  varchar ( returns varchar) as       begindeclare  @rtnVal  varchar (+);   declare @i int;  declare @ Jsonkey varchar (;  declare ) @jsonvalue  varchar (+);   declare @ Json varchar (8000);  declare  @tmprow  varchar ();  declare  @tmpval  varchar,     if (@p_jsonstr  is not null) begin      SET  @json  = replace (@p_jsonstr,  ' {',  ');     set  @json  = replace (@json,  '} ',   ');     set  @json  = replace (@json,  ' "',  ');D eclare   @json_cur  CURSOR;  --  declaration outer cursor set  @json_cur  = CURSOR FOR  Select tid from fn_split (@json,  ', ');open  @json_cur--  Open cursor (outer cursor) Fetch next  FROM  @json_cur  INTO  @tmprow--  extract outer cursor row while (@ @FETCH_STATUS  = 0) beginif (@ Tmprow is not null) beginset @i = 0; set  @jsonkey  =  "; set  @jsonvalue  =  ';D eclare  @str_cur  CURSOR;--  declares an inner-layer cursor set  @str_cur  =  cursor for select tid from fn_split (@tmprow,  ': ');--the second time after the split cursor (inner cursor) OPEN   @str_cur   --  Open cursor fetch next from  @str_cur  INTO  @tmpval--  Extract inner cursor row while (@ @FETCH_STATUS  = 0) beginif (@i = 0)  BEGINSET  @jsonkey  =  @tmpvalENDIF (@i = 1) beginset   @jsonvalue  =  @tmpvalENDSET  @i = @i + 1FETCH NEXT FROM  @str_cur  into  @tmpval--  inner cursor moves down one line endclose  @str_cur--  closes the inner-layer cursor deallocate  @str_cur  --  release inner cursor if (@jsonkey  =  @p_key) beginset  @rtnVal  =  @jsonvalueENDENDFETCH  NEXT FROM  @json_cur  INTO  @tmprow--  The inner cursor ends, the outer cursor moves down one line endclose  @json_cur--   Close the outer cursor deallocate  @json_cur--  Release the outer cursor end     return  @rtnVal    end

Examples of Use:

Select Dbo.fn_parsejson (' {"Billprice": "1.67", "buydate": "2009-12-30", "Currentstatus": "Has a lease", "Decoration": "Hardcover", " Empcode ":" 174999 ",", "Houseid": "f20bebe259794c858c76122bcbacc71f", "id": "36398", "Isaccompany": "0", "iscollect": "1 "," Isonlyone ":" 1 "," Isshotproperty ":" 1 "," Nocollectreason ":" "," Parking ":" 0 "," Propertyno ":" 5885109 "," recorddate ": "2017-06-08", "Recordremark": "Ah ah ah ah ah ah ah ah o ah ah ah ah ah,"} ', ' Buydate ')

This article is from the "Wind Trace _ Snow Tiger" blog, please be sure to keep this source http://snowtiger.blog.51cto.com/12931578/1942790

SQL Server parsing JSON strings