MySql语句备忘 JSON截取

INSERT INTO DONGZ_TMP_AuditCreditAmount (OrderId, AuditCreditAmount,UpdateTime) SELECT SUBSTRING(OrderDetail,
LOCATE('"OrderId":',OrderDetail)+11,

LOCATE(',',OrderDetail,LOCATE('"OrderId":',OrderDetail))-1-(LOCATE('"OrderId":',OrderDetail)+11)) AS OrderId
,
SUBSTRING(OrderDetail,LOCATE('"AuditCreditAmount":',OrderDetail)+20,IF(LOCATE(',',OrderDetail,11) =0,LOCATE('}',OrderDetail,2),LOCATE(',',OrderDetail,LOCATE('"AuditCreditAmount":',OrderDetail)))-(LOCATE('"AuditCreditAmount":',OrderDetail)+20)) AS AuditCreditAmount
,SUBSTRING(OrderDetail,
LOCATE('"UpdateTime":',OrderDetail)+14,

LOCATE(',',OrderDetail,LOCATE('"UpdateTime":',OrderDetail))-1-(LOCATE('"UpdateTime":',OrderDetail)+20)) AS UpdateTime
FROM tbs where FlowName ='总部初审'

posted @ 2018-02-24 10:26  dongzhou  阅读(421)  评论(0)  编辑  收藏  举报