Urgent! - How to extract number from a string

• Jeff Moden

  SSC Guru

  Points: 1004593

  More actions

  July 10, 2018 at 7:31 am

  #1996942

  dva2007 - Tuesday, July 10, 2018 6:55 AM

  After reading the article above I used dbo.DigitsOnlyEE which is very quick.
  

  Exactly.  That's why I wanted to see what you were using.  Thank you for taking the time on the feedback here.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• dva2007

  SSCertifiable

  Points: 7707

  More actions

  July 10, 2018 at 7:47 am

  #1996947

  No problem. Thank you to all who took time out to respond.

• sudishnch

  Newbie

  Points: 5

  More actions

  March 14, 2021 at 6:28 am

  #3857524

  DECLARE @EMAIL AS VARCHAR(100)='support_dso104@itradeerp.com'

  DECLARE @NUMBERS AS VARCHAR(100)='',@ALPHA AS VARCHAR(100)='',@SPECIAL AS VARCHAR(100)=''

  ;WITH TREE AS

  (

  SELECT SUBSTRING(@EMAIL,1,1) AS CHR, CAST(1 AS INT) AS LVL

  UNION ALL

  SELECT SUBSTRING(@EMAIL,LVL+1,1) AS CHR, CAST(LVL+1 AS INT) AS LVL FROM TREE WHERE LEN(@EMAIL)>LVL

  )

  SELECT @NUMBERS=COALESCE(@NUMBERS + '', '')+ IIF(CHR LIKE '%[0-9]%', CHR, ''),@ALPHA=COALESCE(@ALPHA + '', '')+ IIF(CHR LIKE '%[a-zA-Z]%', CHR, ''),@SPECIAL=COALESCE(@SPECIAL + '', '')+ IIF(CHR NOT LIKE '%[a-zA-Z0-9]%', CHR, '') FROM TREE

  SELECT @NUMBERS AS Num,@ALPHA AS Alph,@SPECIAL AS Spl