# Extract the num Value from String

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

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?)

``````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``````