Allow Only Numbers

  • Scott Williams

    SSC Enthusiast

    Points: 131

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

  • Jeff Moden

    SSC Guru

    Points: 994677

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

    RETURNS NVARCHAR(4000)

    AS

    BEGIN

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

    FROM dbo.Tally

    WHERE N <= LEN(@String)

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

    OR

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

    ORDER BY N DESC

    RETURN @String

    END

    Tally table may be found here...

    http://www.sqlservercentral.com/articles/TSQL/62867/

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

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

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