Post reply

Allow Only Numbers

  • June 1, 2008 at 3:09 am

    #182938

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

  • June 1, 2008 at 9:48 pm

    #822763

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

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

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