GSquared (3/17/2008)
I haven't tested this enough to be sure, but it should work:
Absolutely the right idea with the numbers table, Gus 🙂 but you don't need the CTE or a derived table.
CREATE FUNCTION dbo.fnDigitsOnly (@pString VARCHAR(8000))
-- Tally table can be found at http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @CleanString VARCHAR(8000)
SELECT @CleanString = ISNULL(@CleanString,'')+SUBSTRING(@pString,N,1)
FROM dbo.Tally WITH (NOLOCK)
WHERE N<=LEN(@pString)
AND SUBSTRING(@pString,N,1) LIKE ('[0-9]')
RETURN @CleanString
END
--Jeff Moden
Change is inevitable... Change for the better is not.