June 1, 2008 at 3:09 am
Comments posted to this topic are about the item Allow Only Numbers
June 1, 2008 at 9:48 pm
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
Change is inevitable... Change for the better is not.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy