Count occurrences of a special word in a string in SQL-CodePudding

Using SQL, I want to find the number of occurrence of particular word in a string.

For example I want to count the number of occurrence the word NodeId in the following string:

[{"NodeId":4,"NodeCondition":"1 > 2 AND 1=1"},{"NodeId":5,"NodeCondition":"56 %2 = 0"}]

CodePudding user response：

You have a JSON string. The most accurate way of doing this is to shred the JSON and count how many NodeId keys you have.

DECLARE @String nvarchar(max) = '[{"NodeId":4,"NodeCondition":"1 > 2 AND 1=1"},{"NodeId":5,"NodeCondition":"56 %2 = 0"}]';

SELECT COUNT(*)
FROM OPENJSON(@String)
  WITH (NodeId int)
WHERE NodeId IS NOT NULL;

db<>fiddle

Note that just counting how many times you have NodeId in the string is not good enough, as it may be in one of the values, not in a key, or it may be in a key but not the whole key name.

CodePudding user response：

Remove the word NodeId from the string and then compare it with the outcome string.

Try this code:

DECLARE @string NVARCHAR(MAX) = '[{"NodeId":4,"NodeCondition":"1 > 2 AND 1=1"},{"NodeId":5,"NodeCondition":"56 %2 = 0"}]';
DECLARE @stringLEN INT = LEN(@string);
DECLARE @tmpString INT = (SELECT LEN(REPLACE(@string, 'NodeId', '')));

SELECT (@stringLEN - @tmpString) / LEN('NodeId');