Extract the num Value from String

1

Hi,
how can extract the num Value from this String?. Num-Value can have var length.

Start: =
End: )

'#BAVDB#(RowCount=3204)'

Result: 3204

Regards
Nicole

2

Naively:

declare @str varchar(155) = '#BAVDB#(RowCount=3204)'   
 select CHARINDEX('=', @str)
      , CHARINDEX(')', @str)
      , SUBSTRING(@str, CHARINDEX('=', @str)+1, CHARINDEX(')', @str) - CHARINDEX('=', @str) - 1)
      , CAST(SUBSTRING(@str, CHARINDEX('=', @str)+1, CHARINDEX(')', @str) - CHARINDEX('=', @str) - 1) AS int)

but we can use CROSS APPLY to compute the sub-expressions and keep things DRYer:

select val from (values(@str)) v(s)
cross apply (select SUBSTRING(s, CHARINDEX('=', s)+1, len(s))) _1(start)
cross apply (select SUBSTRING(start, 1, CHARINDEX(')', start) - 1)) _2(string)
cross apply (select CAST(string as int)) _3(val)

There should be no different in run time between the two approaches
Also, there is no error checking here. (what if the input string is malformed?)

3 
SELECT string,
    SUBSTRING(string, start_of_num_value, length_of_num_value) AS num_value
FROM (
    VALUES('#BAVDB#(RowCount=3204)'),
          ('#BAVDB#=ABC=(RowCount=3204)'),
	  ('#BAVDB#=ABC=(RowCount=3204'),
	  ('#BAVDB#=ABC=(RowCount)')
) AS test_data(string)
CROSS APPLY (
    SELECT PATINDEX('%=[0-9]%', string) + 1 AS start_of_num_value
) AS ca1
CROSS APPLY (
    SELECT CASE WHEN start_of_num_value < 2 THEN 0 ELSE CHARINDEX(')', string + ')',
        start_of_num_value + 1) - start_of_num_value END AS length_of_num_value
) AS ca2