Allow Only Numbers

• Scott Williams

  SSC Enthusiast

  Points: 131

  More actions

  June 1, 2008 at 3:09 am

  #182938

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

• Jeff Moden

  SSC Guru

  Points: 995523

  More actions

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

  ---

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