SQL将JSON转成列
好久不写东西,这个也没什么技术含量,放上来玩玩,也许有人用的着。
/** * create procedure for get all fields from json * * Mark * * 2014-7-17 17:16:01 * * */ USE QEO_Insurance GO IF OBJECT_ID('sp_getJSONFields') IS NOT NULL DROP PROCEDURE sp_getJSONFields GO CREATE PROCEDURE sp_getJSONFields @Json VARCHAR(MAX) AS BEGIN SELECT @Json=REPLACE(@Json,'{','') SELECT @Json=REPLACE(@Json,'}',',') DECLARE @temp VARCHAR(100) DECLARE @objName VARCHAR(30) DECLARE @objValue VARCHAR(30) DECLARE @fieldSql VARCHAR(MAX) SET @fieldSql='select ' WHILE LEN(@Json)>0 BEGIN SELECT @temp=SUBSTRING(@Json,0,CHARINDEX(',',@Json,0)) --PRINT @temp SELECT @Json=RIGHT(@Json,LEN(@Json)-LEN(@temp)-1) --PRINT @Json set @objName =left(@temp,CHARINDEX(':',@temp,0)-1) set @objValue =right(@temp,len(@temp)-CHARINDEX(':',@temp,0)) --PRINT @objName+'='+ @objValue+';' set @fieldSql=@fieldSql+REPLACE(@objValue,'"','''')+' as '+REPLACE(@objName,'"','')+',' --PRINT '------------------' END SET @fieldSql=LEFT(@fieldSql,LEN(@fieldSql)-1) --EXEC sp_executesql @fieldSql EXEC (@fieldSql) END GO EXEC sp_getJSONFields @json= '{"VIN_Invalid":"1","VIN_ID":"427658","Veh_TypeCode":"CTRK","Year":"2011","Make":"TOYOTA","Veh_Model":"TUNDRA","Body_CD":"PK"}'
输入:
{"VIN_Invalid":"1","VIN_ID":"427658","Veh_TypeCode":"CTRK","Year":"2011","Make":"TOYOTA","Veh_Model":"TUNDRA","Body_CD":"PK"}
输出:
2014-07-1719:38:41