Shulou(Shulou.com)06/01 Report--

-parsing string function- -CREATE FUNCTION [dbo]. [fn_split] (@ p_str VARCHAR (8000) @ p_split VARCHAR (10) RETURNS @ tab TABLE (tid VARCHAR (2000)) 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 -- parsing JSON string-- p_jsonstr json string-- p_key key-- returns the value CREATE FUNCTION [dbo]. [fn_parsejson] (@ p_jsonstr VARCHAR (8000) corresponding to p_key P_key VARCHAR (3000) RETURNS VARCHAR (3000) AS BEGINDECLARE @ rtnVal VARCHAR (3000) DECLARE @ i INT; DECLARE @ jsonkey VARCHAR (1000); 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;-declare the outer cursor SET @ json_cur = CURSOR FOR SELECT tid FROM fn_split (@ json,','); OPEN @ json_cur-- open the cursor (outer cursor) FETCH NEXT FROM @ json_cur INTO @ tmprow-- to extract the outer cursor line WHILE (@ @ FETCH_STATUS = 0) BEGINIF (@ tmprow IS NOT NULL) BEGINSET @ I = 0th set @ jsonkey ='; SET @ jsonvalue ='; DECLARE @ str_cur CURSOR Declare the inner cursor SET @ str_cur = CURSOR FOR SELECT tid FROM fn_split (@ tmprow,':') -- after the second split, the cursor (inner cursor) OPEN @ str_cur-- opens the cursor FETCH NEXT FROM @ str_cur INTO @ tmpval-- to extract the inner cursor line 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-- Close the inner cursor DEALLOCATE @ str_cur-after releasing the inner cursor IF (@ jsonkey = @ p_key) BEGINSET @ rtnVal = @ jsonvalueENDENDFETCH NEXT FROM @ json_cur INTO @ tmprow-- The outer cursor moves down one line ENDCLOSE @ json_cur-- closes the outer cursor DEALLOCATE @ json_cur-- releases the outer cursor END RETURN @ rtnVal END

Examples of use:

Select dbo.fn_parsejson ('{"billPrice": "2009-12-30", "currentStatus": "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"}', 'buyDate')

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.