InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 11:07:41 GMT20http://www.sqlservercentral.com/Forums/Topic509558-439-1.aspxHi Scott...You have some minor errors you need to fix... (found in 2k, and 2k5)[color="RED"]Msg 102, Level 15, State 1, Procedure fnc_AllowNumbersOnly, Line 8Incorrect syntax near ' '.Msg 102, Level 15, State 1, Procedure fnc_AllowNumbersOnly, Line 9Incorrect syntax near ' '.[/color]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...[code] CREATE FUNCTION dbo.fnDigitsOnly (@String NVARCHAR(4000))RETURNS NVARCHAR(4000) AS BEGIN SELECT @String = STUFF(@String,N,1,'') FROM dbo.Tally WHERE N &lt;= LEN(@String) AND (SUBSTRING(@String,N,1) &lt; '0' OR SUBSTRING(@String,N,1) &gt; '9') ORDER BY N DESC RETURN @String END[/code]Tally table may be found here...[url]http://www.sqlservercentral.com/articles/TSQL/62867/[/url]Sun, 01 Jun 2008 21:48:03 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic509558-439-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Numbers+Only/63174/">Allow Only Numbers</A>[/B]Sun, 01 Jun 2008 03:09:35 GMTScott Williams