SQL 中的子字符串与来自模式

Question

我正在努力在SQL中子字符串和获取值。

我有一个如下所示的 JSON 字符串：

DECLARE @string varchar(max)= '[{"CustomFieldId":18,"FieldName":"ABCD","FieldType":"freeText","FieldValue":null,"Details":null,"Value":null,"RelationTable":null,"Isisible":true,"IsAdmin":false,"CreatedDate":null,"ModifiedDate":null,"LoggedInUser":"TESTUSER"},{"CustomFieldId":19,"FieldName":"Workdomain","FieldType":"freeText","FieldValue":null,"Details":null,"Value":null,"RelationTable":null,"IsVisible":true,"IsAdmin":false,"CreatedDate":null,"ModifiedDate":null,"LoggedInUser":"149645"},{"CustomFieldId":20,"FieldName":"TEST1234","FieldType":"freeText","FieldValue":"Sometest","Details":null,"Value":null,"RelationTable":null,"IsVisible":false,"IsAdmin":false,"CreatedDate":null,"ModifiedDate":null,"_listlovFields":[],"org4Values":[],"LoggedInUser":"TESTUSER"}]'

或者也可以是这样的：

DECLARE @string varchar(max) = '[{"CustomFieldId":20,"FieldName":"TEST1234","FieldType":"freeText","FieldValue":"Sometest","Details":null,"Value":null,"RelationTable":null,"IsVisible":false,"IsAdmin":false,"CreatedDate":null,"ModifiedDate":null,"LoggedInUser":"TESTUSER"}]'

现在，我需要从其中任何一个获取具有特定"字段名称"的特定"CustomFieldId"的"字段值"，并且字段值以特定字符串开头。

就像，我将得到这些：

declare @propName varchar(max) = 'Test1234',
@customFieldId varchar(max) = 20,
@value varchar(max) = 'Some'

因此，在这种特殊情况下，我需要获取自定义字段的字段值，其中 CustomFieldId："20"，FieldName"："TEST1234"，其中 FieldValue 以"Some"开头。

输出只需要是字符串"Sometest"，因为这是 CustomFieldId 的 FieldValue："20">

任何这方面的帮助将不胜感激。

Answer 1

当你在SQL服务器上安装正则表达式函数时，这很容易解决。我已经包含了您可以找到它们的链接。

https://www.simple-talk.com/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-example/

declare @CustomFieldId nvarchar(50)= '20'
declare @FieldName nvarchar(50) = 'TEST1234'
declare @FieldValueStartsWith nvarchar(50) = 'Some'
declare @input nvarchar(500)
select @input = '[{"CustomFieldId":20,"FieldName":"TEST1234","FieldType":"freeText","FieldValue":"Sometest","Details":null,"Value":null,"RelationTable":null,"IsVisible":false,"IsAdmin":false,"CreatedDate":null,"ModifiedDate":null,"LoggedInUser":"TESTUSER"}]'
declare @result nvarchar(500)
declare @expression nvarchar(200) = '.*"CustomFieldId":' + @CustomFieldId + ',"FieldName":"' + @FieldName + '".*"FieldValue":("' + @FieldValueStartsWith + 'w+").*'
select @result = dbo.RegExReplaceX(@expression,@input,'$1',dbo.RegExOptionEnumeration(0,0,0,0,1,0,0,0,0)) -- OPTION = FOR CASE SENSITIVE 
if @result = @input SELECT NULL ELSE SELECT @RESULT