CODEWITHSUNDEEP
sqlsql-serverlogicvarchar
Hari
Hari

Reputation: 88

Find an unmatched curly brackets in a string in SQL

How can I find an unmatched curly brackets in a string in SQL?

DECLARE @iVariable varchar(100)
SET iVariable = '{Day}{Month}{Year}'

If any unmatched left bracket found ({Day}{Month{Year}) then should return 'Unmatched {'

else any unmatched right bracket found ({Day}{Month}Year}) then should return 'Unmatched }'

If there is no unmatched brackets it shoud return the value in comma seperated format eg ('Day,Month,Year')

Is there any logic to do this?

Upvotes: 3

Views: 1821

Answers (2)

Gunnar Sjúrðarson Knudsen
Gunnar Sjúrðarson Knudsen

Reputation: 441

What I would do is that I would validate the length of the string after replacing '{' with '' (empty string).

DECLARE @iVariable varchar(100) = '{Day}{Month}{Year}'

select case 
   when len(@iVariable) -  len(replace(@iVariable, '{', '')) < len(@iVariable) -  len(replace(@iVariable, '}', '')) 
   then 'Unmatched }'
   when len(@iVariable) -  len(replace(@iVariable, '{', '')) > len(@iVariable) -  len(replace(@iVariable, '}', '')) 
   then 'Unmatched {'
   else right(replace(replace(@iVariable, '{', ','), '}', ''), len(replace(replace(@iVariable, '{', ','), '}', '')) - 1)
end

What happens here is that I check if there are more '}' than '{', it returns unmatched '}'. Likewise for '}'.

If the number matches, it returns the original string, with '{' and '}' replaced out, and commas inserted instead.

Edit: As Gordon stated in the comments, this does not work for for example '{}}{'.

Instead, You could use a user defined function. For instance:

create function SomeFunc(@iVariable varchar(2000))
returns varchar(3000)
as
begin

if len(replace(replace(@iVariable, '}', ''), '{', '')) = 0 
   return 'No data present'
else
begin

   -- Declare stuff to be used
   declare @result varchar(3000) = ''
   declare @AMT_Left  int = len(@iVariable) -  len(replace(@iVariable, '{', ''))
   declare @AMT_Right int = len(@iVariable) -  len(replace(@iVariable, '}', ''))


   -- First test if no. of brackets match:
   if @AMT_Left > @AMT_Right 
      set @result = 'Unmatched }'
   else if @AMT_Left < @AMT_Right 
      set @result = 'Unmatched {'
   else if @AMT_Left = @AMT_Right
   begin
   -- If matched, define result, and use while loop for error handling
      set @result = right(replace(replace(@iVariable, '{', ','), '}', ''), len(replace(replace(@iVariable, '{', ','), '}', '')) - 1)
      DECLARE @intFlag INT
      SET @intFlag = 1
      -- Loop through each set and check if '{' occurs before '}':
      WHILE (@intFlag <= @AMT_Left and @result != 'Non matching pair')
      BEGIN
         if charindex('{', @iVariable) > charindex('}', @iVariable)
            set @result =  'Non matching pair'

         set @iVariable = right(@iVariable, len(@iVariable) - charindex('}', @iVariable))
         SET @intFlag = @intFlag + 1
      end
   end

end
return @result
end;

go

Testing with these input values:

select dbo.SomeFunc('{Day}{Month}{Year}')
select dbo.SomeFunc('{Day}{Month{Year}')
select dbo.SomeFunc('{Day}{Month}Year}')
select dbo.SomeFunc('{}{}')
select dbo.SomeFunc('{}}{')
select dbo.SomeFunc('{Day}}Month{')

result: 

Day,Month,Year
Unmatched }
Unmatched {
No data present
No data present
Non matching pair

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1271003

There may be a more elegant way to do this, but the following should capture all the cases:

with v as (
      select '{Day}{Month}{Year}' as var union all
      select '{Day}{Month}{Year}}{' union all
      select '{Day}{Month}{Year}}}'
     ),
     cte as (
      select left(var, 1) as c, 1 as num, var
      from v
      union all
      select substring(var, num+1, 1), num + 1, var
      from cte
      where num <= len(var)
     ) 
select var,
       (case when min(balance) < 0 then 'Unbalanced }'
             when sum(case when c = '{' then 1
                           when c = '}' then -1
                           else 0
                      end) > 0
             then 'Unbalanced {'
             else 'Balanced'
       end)
from (select cte.*,
             (select sum(case when c = '{' then 1
                              when c = '}' then -1
                              else 0
                         end)
              from cte cte2
              where cte2.var = cte.var and cte2.num <= cte.num
             ) as balance
      from cte
     ) t
group by var;

This explodes the values character by character and then checks for the balance.

Upvotes: 4

Related Questions