Pages

Sep 11, 2011

SQL Function to remove numbers from a varchar

SQL Function to remove numbers from a varchar database field
CREATE FUNCTION dbo.RemoveSpecialChars (@s VARCHAR(MAX))
RETURNS VARCHAR(MAX)
WITH schemabinding
BEGIN
IF @s is null
RETURN null
DECLARE @s2 VARCHAR(MAX)
SET @s2 = ''
DECLARE @l int
SET @l = len(@s)
DECLARE @p int
SET @p = 1
WHILE @p <= @l BEGIN
DECLARE @c int
SET @c = ascii(substring(@s, @p, 1))
IF not @c between 48 and 57
SET @s2 = @s2 + char(@c)
SET @p = @p + 1
END
IF len(@s2) = 0
RETURN null
RETURN @s2
END
usage:
select dbo.RemoveSpecialChars('Test-123String')
output:
Test-String