Allow Only Numbers

  • Comments posted to this topic are about the item Allow Only Numbers

  • Hi Scott...

    You have some minor errors you need to fix... (found in 2k, and 2k5)

    Msg 102, Level 15, State 1, Procedure fnc_AllowNumbersOnly, Line 8

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure fnc_AllowNumbersOnly, Line 9

    Incorrect syntax near '?'.

    The cause of the errors is 4 hidden characters just before the DECLARE and the WHILE. Past the code into MS Word to see them.

    Also, consider getting rid of the @lenPhone and @phoneStr variables since they're declared but not used anywhere.

    If I may suggest, consider not using the WHILE loop at all... consider the Tally table which makes things run more than twice as fast when a fair number non-digit characters are present and still about 20% faster than when they are not.... Yeah, I know... I could use LIKE... but the OR, in this case, shaves an extra second off a 10,000 row run when there's more than just a couple of non-numeric digits...

    CREATE FUNCTION dbo.fnDigitsOnly

    (@String NVARCHAR(4000))




    SELECT @String = STUFF(@String,N,1,'')

    FROM dbo.Tally

    WHERE N <= LEN(@String)

    AND (SUBSTRING(@String,N,1) < '0'


    SUBSTRING(@String,N,1) > '9')


    RETURN @String


    Tally table may be found 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)
    Intro to Tally Tables and Functions

Viewing 2 posts - 1 through 2 (of 2 total)

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