# Urgent! - How to extract number from a string

• 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.

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

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

• 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

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

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