How to check for number of parentheses in a String - SQL

Question

I have an input string in the following format:

DECLARE @input VARCHAR(2000)

SET @input = '11.98, 123.007(p), 11.999, 12.998(p)(q),11.98(p)(q)(r)'
While looping through each item in the list, I need to check the string for the number of parentheses it contains: one () or two () or three ().

If there was one (), I was able to differentiate '11.98', '123.007(p)' using

CharIndex('(', @EACHINPUTITEMFROMLIST)
How do i check if the string has two or three parentheses? Please assist.

Answer 1

If your SQL server version is 2016 or later, try this:

DECLARE @s varchar(20) = '11.98(p)(q)(r)';
SELECT COUNT(*) - 1 FROM STRING_SPLIT(@s, '(')

Answer 2

Try an example:

--declare @EACHINPUTITEMFROMLIST varchar(max) = '11.98'  
--declare @EACHINPUTITEMFROMLIST varchar(max) = '123.007(p)'  
declare @EACHINPUTITEMFROMLIST varchar(max) = '12.998(p)(q)'  
--declare @EACHINPUTITEMFROMLIST varchar(max) = '11.98(p)(q)(r)'  
  
  
select   
    case   
    when a = 0 then 'no'   
    when c = 0 then 'one'  
    when e = 0 then 'two'  
    else 'three or more' end HowMany  
from (values (charindex('(', @EACHINPUTITEMFROMLIST))) t1(a)  
cross apply (values (charindex(')', @EACHINPUTITEMFROMLIST, a))) t2(b)  
cross apply (values (charindex('(', @EACHINPUTITEMFROMLIST, b))) t3(c)  
cross apply (values (charindex(')', @EACHINPUTITEMFROMLIST, c))) t4(d)  
cross apply (values (charindex('(', @EACHINPUTITEMFROMLIST, d))) t5(e)  

Searching for ‘)’ can be removed; the query can be simplified.

Answer 3

Another approach.

SQL

DECLARE @input VARCHAR(2000) = '11.98, 123.007(p), 11.999, 12.998(p)(q),11.98(p)(q)(r)';
DECLARE @StringToFind CHAR(1) = '(';

SELECT @input AS inputString
      , (LEN(@input) - LEN(REPLACE(@input, @StringToFind,'')))/COALESCE(NULLIF(LEN(@StringToFind), 0), 1) AS cnt;

Output

+--------------------------------------------------------+-----+
|                      inputString                       | cnt |
+--------------------------------------------------------+-----+
| 11.98, 123.007(p), 11.999, 12.998(p)(q),11.98(p)(q)(r) |   6 |
+--------------------------------------------------------+-----+