Wednesday, 3 September 2014

How to remove special characters from a string (non alpha numeric characters) in sql server


CREATE FUNCTION [dbo].[StripNonAlphaNumerics]
(
@s VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @p INT = 1, @n VARCHAR(8000) = '';
WHILE @p <= LEN(@s) BEGIN IF SUBSTRING(@s, @p, 1) LIKE '[A-Za-z0-9]' BEGIN SET @n += SUBSTRING(@s, @p, 1); END SET @p += 1; END RETURN(@n); END

Ex : select dbo.StripNonAlphaNumerics('0xyz^ dd')

No comments:

Post a Comment