Extract Num-Value from String

• info 58414

  Hall of Fame

  Points: 3386

  More actions

  January 13, 2017 at 12:51 am

  #333818

  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

• DesNorton

  SSC-Insane

  Points: 24279

  More actions

  January 13, 2017 at 3:44 am

  #1922516

  
DECLARE @String VARCHAR(50) = '#BAVDB#(RowCount=3204)';

  -- As a single statement
SELECT NumVal = SUBSTRING(@String, CHARINDEX('=', @String) +1, CHARINDEX(')', @String, CHARINDEX('=', @String) +1) - CHARINDEX('=', @String) -1);

  -- With a cte to identify the start and end positions
WITH ctePos AS (
SELECT
  StartPos = CHARINDEX('=', @String)
  , EndPos = CHARINDEX(')', @String, CHARINDEX('=', @String) +1)
)
SELECT NumVal = SUBSTRING(@String, StartPos +1, EndPos - StartPos -1)
FROM ctePos;


  ---

  How to post data/code on a forum to get the best help.

• sajikv2007

  SSC Veteran

  Points: 257

  More actions

  January 16, 2017 at 1:51 am

  #1922894

  Try this :

  DECLARE @String VARCHAR(50) = '#BAVDB#(RowCount=3204)';

  SELECT REPLACE(Right(@String,LEN(@String)-CHARINDEX('=',@String)),')','')