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".
    "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)

  • 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 33 (of 33 total)

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