Remove Non-Numeric Character in SQL String Expression
This T-SQL tutorial includes a user-defined SQL Server function code to clear alpha characters from an input string expression. Database programmers can use the given user-defined function in their development codes for removing characters except numbers from 0 to 9.
Here is a very basic user defined SQL function which removes unwanted alpha characters which does not represent numbers in a string expression
If database developers check the regular expression used in PatIndex SQL function, they will see that the regular expression continuously checking characters representing characters except 0 to 9.
create function ClearNonNumericCharacters(@str nvarchar(max))
returns nvarchar(max)
as
begin
while patindex('%[^0-9]%', @str) > 0
set @str = stuff(@str, patindex('%[^0-9]%', @str), 1, '')
return @str
end
Here to use user defined SQL Server function in a sample case for Transact-SQL developers
select dbo.ClearNonNumericCharacters('19Kodyaz0 4*6/2=? QA=>9')
select phone, dbo.ClearNonNumericCharacters(phone) from PhoneNumbers
Here is the output of above sample SQL function
This SQL function code can be used after changing the PatIndex regular expression part to remove numeric characters in SQL string expression.