Urgent! - How to extract number from a string

Question

Urgent! - How to extract number from a string

Viewing 3 posts - 31 through 33 (of 33 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 998742 More actions · Answer 1

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".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

dva2007 SSCertifiable Points: 7705 More actions · Answer 2

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

sudishnch Newbie Points: 5 More actions · Answer 3

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