时间戳

首页 > 解决方案 > 如何计算 SQL 文本中关键字的实例

sql - 如何计算 SQL 文本中关键字的实例

问题描述

我有一个包含行的表，其中一列是text存储 Json 的类型。我想计算有多少 Json 数据项（我想计算每个字符串“aodata.id”的实例）并将该数字存储在int名为JsonRowsInThis.

我试着像这样选择

SELECT 
    value  
FROM 
    STRING_SPLIT((select top 1 MLSImportedGroupsOfItems_ImportedContent from RE_MLSImportedGroupsOfItems), 'odata.id')

但我使用的是 SQL Server 2014 并且string_split不适用于它。

是否有解决方案来计算使用替换的字符数，但它不适用于我的情况？

下面一个单元格的内容示例（部分内容，很长）

{"@odata.context":"https://mywebsite.com/$metadata#Media","value":[{"@odata.id":"https://mywebsite.com/Media('5b9f36ec5928d64cdb53a8f7')","MediaKey":"5b9f36ec5928d64cdb53a8f7","ImageWidth":290,"ImageHeight":218,"ImageSizeDescription":"290x218","MediaURL":"https://s3.Cloudserver24.com/mlsgrid/images/eb726144-0302-4f04-a1b1-554b8a476ba3.jpeg","Modified":"2019-02-03T11:18:18.300Z","ModificationTimestamp":"2019-02-03T11:18:18.300Z","ResourceRecordKey":"CAR28791079","ResourceRecordID":"CARNCM530248","ResourceName":"property","OriginatingSystemName":"carolina","MlgCanView":true,"Order":"0"},{"@odata.id":"https://mywebsite.com/Media('5b9f36ec5928d64cdb53a8fa')","MediaKey":"5b9f36ec5928d64cdb53a8fa","ImageWidth":290,"ImageHeight":218,"ImageSizeDescription":"290x218","MediaURL":"https://s3.Cloudserver24.com/mlsgrid/images/82120215-cf3c-44b2-9786-cadd60d68db0.jpeg","Modified":"2019-02-03T11:18:18.300Z","ModificationTimestamp":"2019-02-03T11:18:18.300Z","ResourceRecordKey":"CAR28791079","ResourceRecordID":"CARNCM530248","ResourceName":"property","OriginatingSystemName":"carolina","MlgCanView":true,"Order":"3"},{"@odata.id":"https://mywebsite.com/Media('5b9f36ec5928d64cdb53a8f8')","MediaKey":"5b9f36ec5928d64cdb53a8f8","ImageWidth":290,"ImageHeight":218,"ImageSizeDescription":"290x218","MediaURL":"https://s3.Cloudserver24.com/mlsgrid/images/641b2e26-f022-44af-bfbf-c482df6a873c.jpeg","Modified":"2019-02-03T11:18:18.300Z","ModificationTimestamp":"2019-02-03T11:18:18.300Z","ResourceRecordKey":"CAR28791079","ResourceRecordID":"CARNCM530248","ResourceName":"property","OriginatingSystemName":"carolina","MlgCanView":true,"Order":"1"},{"@odata.id":"https://mywebsite.com/Media('5b9f36ec5928d64cdb53a903')","MediaKey":"5b9f36ec5928d64cdb53a903","ImageWidth":290,"ImageHeight":218,"ImageSizeDescription":"290x218","MediaURL":"https://s3.Cloudserver24.com/mlsgrid/images/066c658b-ede7-4f90-9cae-ef71774bc152.jpeg","Modified":"2019-02-03T11:18:18.300Z","ModificationTimestamp":"2019-02- etc ......}

标签： sqljsonsql-serversql-server-2014

解决方案

您可以像这样计算值：

select ( len(value) - len(replace(value, 'aodata.id')) ) / len('aodata.id) as num_aodata_id

编辑：

您不应该使用text数据类型。您可以通过转换为varchar(max)：

select ( len(v.val) - len(replace(v.val, 'aodata.id')) ) / len('aodata.id) as num_aodata_id
from t cross apply
     (values (convert(varchar(max), t.value)) v(val)

推荐阅读