SQLTeam.com | Weblogs | Forums

Removing special characters


#1

i have this kind of data in a column

Below 50AH & (100-200 AH) 2001-5000 VA

and it should give the below output

Below_50AH_100_200_AH_2001_5000_VA

i tired.. creating this function

CREATE FUNCTION dbo.RemoveSpecialChars (@s VARCHAR(256)) RETURNS VARCHAR(256)
WITH SCHEMABINDING
BEGIN
IF @s is null
RETURN null
DECLARE @s2 varchar(256) = '', @p INT = 1, @l INT, @c INT
SET @l = LEN(@s)

WHILE @p <= @l 
BEGIN

	SET @c = ASCII(SUBSTRING(@s, @p, 1))
	IF @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122
		SET @s2 = @s2 + CHAR(@c)
	SET @p = @p + 1
  END

IF LEN(@s2) = 0
RETURN null
RETURN @s2
END

i got this output

Below_50AH_100200_AH_20015000_VA which is wrong


#2
select    replace(replace(replace(replace(replace(replace('Below 50AH & (100-200 AH) 2001-5000 VA', '&', ''), '(', ''), ')', ''), '-', '_'), ' ', '_'), '__', '_')

#3

i have similar lots of different data in the table.. so looking for function


#4

just make it a function