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

Brian collins 141 Reputation points
2021-01-22T15:54:06.623+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,913 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,564 questions
{count} votes

2 additional answers

Sort by: Most helpful
  1. Viorel 113.2K Reputation points
    2021-01-22T16:54:35.877+00:00

    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.

    0 comments No comments

  2. Yitzhak Khabinsky 25,206 Reputation points
    2021-01-22T16:56:52.857+00:00

    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 |
    +--------------------------------------------------------+-----+
    
    0 comments No comments