需要在sql server中解析长字符串中的文本

eit6fx6z 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(193)

我需要从下面的ntext中检索的文本是“elapsedtime”：83775
{“timeline”：{“events”：[{“date”：159361382288，“types”：[“start”]}，{“date”：1593613906603，“types”：[“stop”]}}，“ongoingsladata”：null，“completesladata”：[{“succeed”：true，“goaltime”：57600000，“goaltimeunits”：{“weeks”：0，“remainingdayswithinweek”：1，“remainingmilliswithinday”：2880000，“breaked”：false}，“elapsedtime”：83775，“remainingtime”：57516225，“remainingtimeindaysandmillis”：{“weeks”：0，“remainingdayswithinweek”：1，“remainingmilliswithinday”：28716225，“breaked”：false}，“calendarname”：“sample 9-5 calendar”，“starttime”：159361382288，“stoptime”：1593613906603}]，“metricid”：48，“definitionchangedate”：0，“definitionchangemsepoch”：0，“goalschangedate”：null，“goalschangemsepoch”：null，“goaltimeUpdateDate”：null，“goaltimeUpdatedsepoch”：null，“metriccreateddate”：1593021654383，“UpdateDate”：1593657022772}

sql

来源：https://stackoverflow.com/questions/62795824/need-to-parse-text-out-of-a-long-string-in-sql-server

1条答案

按热度按时间

xmakbtuz1#

不太好。
假设总有一次 elapsedTime :

DECLARE @s varchar(1000) = '{"timeline":{"events":[{"date":1593613822828,"types":["START"]},{"date":1593613906603,"types":["STOP"]}]},"ongoingSLAData":null,"completeSLAData":[{"succeeded":true,"goalTime":57600000,"goalTimeUnits":{"weeks":0,"remainingDaysWithinWeek":1,"remainingMillisWithinDay":28800000,"breached":false},"elapsedTime":83775,"remainingTime":57516225,"remainingTimeInDaysAndMillis":{"weeks":0,"remainingDaysWithinWeek":1,"remainingMillisWithinDay":28716225,"breached":false},"calendarName":"Sample 9-5 Calendar","startTime":1593613822828,"stopTime":1593613906603}],"metricId":48,"definitionChangeDate":0,"definitionChangeMsEpoch":0,"goalsChangeDate":null,"goalsChangeMsEpoch":null,"goalTimeUpdatedDate":null,"goalTimeUpdatedMsEpoch":null,"metricCreatedDate":1593021654383,"updatedDate":1593657022772}'

DECLARE @i int
SELECT @i = CHARINDEX('elapsedTime":', @s)

DECLARE @j int
SELECT @j = CHARINDEX(',', @s, @i + 13)

SELECT SUBSTRING(@s, @i + 13, @j - @i - 13)

或者，既然是json：

SELECT JSON_VALUE(@s, '$.completeSLAData[0].elapsedTime')

赞(0）回复(0）举报 2021-07-24

我来回答

需要在sql server中解析长字符串中的文本

1条答案

相关问题

热门标签

最新问答