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)