15,794,633 members
See more:
I have string with special characters.so how to
replace special characters in sql like "<[^>]+/\'.{}()#\$*@!:;?>."
Posted
Updated 2-Jan-20 18:54pm
v2

## Solution 5

SQL
```DECLARE @str VARCHAR(400)
DECLARE @expres  VARCHAR(50) = '%[~,@,#,\$,%,&,*,(,),.,!]%'
SET @str = '(remove) ~special~ *characters. from string in sql!'
WHILE PATINDEX( @expres, @str ) > 0
SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX( @expres, @str ), 1 ),''),'-',' ')

SELECT @str```

Member 12331432 4-Apr-17 14:01pm
reererer

## Solution 1

You can use REPLACE[^] funcation of sql server to solve the problem. Try this:
SQL
```DECLARE @text nvarchar(128) = '#124 \$99^@'
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(@text,
'!',''),'@',''),'#',''),'\$',''),'%',''),
'^',''),'&',''),'*',''),' ','')```

--Amit

__TR__ 10-Sep-12 6:46am
5+
_Amy 10-Sep-12 6:52am
Thanks Tejus. :)
Navneet Sharma 26-Jul-13 6:44am
Good solution
_Amy 26-Jul-13 6:45am
Thank you Navneet. :)

## Solution 3

CREATE FUNCTION [FN_REMOVE_SPECIAL_CHARACTER] (
@INPUT_STRING varchar(300))
RETURNS VARCHAR(300)
AS
BEGIN

--declare @testString varchar(100),
DECLARE @NEWSTRING VARCHAR(100)
-- set @teststring = '@san?poojari(darsh)'
SET @NEWSTRING = @INPUT_STRING ;
With SPECIAL_CHARACTER as
(
SELECT '>' as item
UNION ALL
SELECT '<' as item
UNION ALL
SELECT '(' as item
UNION ALL
SELECT ')' as item
UNION ALL
SELECT '!' as item
UNION ALL
SELECT '?' as item
UNION ALL
SELECT '@' as item
UNION ALL
SELECT '*' as item
UNION ALL
SELECT '%' as item
UNION ALL
SELECT '\$' as item
)
SELECT @NEWSTRING = Replace(@NEWSTRING, ITEM, '') FROM SPECIAL_CHARACTER
return @NEWSTRING
END
select dbo.[FN_REMOVE_SPECIAL_CHARACTER] ('@s()antosh')

That's Great. thank you

## Solution 6

Here replace special character in sql table and replace it by normal character

```<pre lang="SQL">

alter proc dbo.specialcharacterreplacer

@tblname varchar(1000),
@column_name varchar(1000)

as
begin

--declare @obj VARCHAR(MAX),
--set @obj= 'select *from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '@tblname''
--exec @obj

declare @Sql VARCHAR(MAX)
set @Sql = '
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ó'''+ ', '+'''o'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ò'''+ ', '+'''o'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ö'''+ ', '+'''o'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ð'''+ ', '+'''o'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ô'''+ ', '+'''o'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''õ'''+ ', '+'''o'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ó'''+ ', '+'''o'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ó'''+ ', '+'''o'''+')

update ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''×'''+ ', '+'''x'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''f'''+ ', '+'''f'''+')

UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ò'''+ ', '+'''O'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ó'''+ ', '+'''O'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ô'''+ ', '+'''O'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Õ'''+ ', '+'''O'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ö'''+ ', '+'''O'''+')

UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ñ'''+ ', '+'''n'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ñ'''+ ', '+'''N'''+')

UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''è'''+ ', '+'''e'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''é'''+ ', '+'''e'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ê'''+ ', '+'''e'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ë'''+ ', '+'''e'''+')

UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''È'''+ ', '+'''E'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''É'''+ ', '+'''E'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ê'''+ ', '+'''E'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ë'''+ ', '+'''E'''+')

UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''á'''+ ', '+'''a'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ã'''+ ', '+'''a'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''â'''+ ', '+'''a'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''à'''+ ', '+'''a'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ä'''+ ', '+'''a'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''å'''+ ', '+'''a'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ª'''+ ', '+'''a'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''æ'''+ ', '+'''ae'''+')

UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''À'''+ ', '+'''A'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Á'''+ ', '+'''A'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Â'''+ ', '+'''A'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ã'''+ ', '+'''A'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Å'''+ ', '+'''A'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ä'''+ ', '+'''A'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Æ'''+ ', '+'''A'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''æ'''+ ', '+'''AE'''+')

UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ù'''+ ', '+'''u'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ú'''+ ', '+'''u'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''û'''+ ', '+'''u'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ü'''+ ', '+'''u'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''µ'''+ ', '+'''u'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ù'''+ ', '+'''U'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ú'''+ ', '+'''U'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Û'''+ ', '+'''U'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ü'''+ ', '+'''U'''+')

UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ý'''+ ', '+'''Y'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ÿ'''+ ', '+'''Y'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ÿ'''+ ', '+'''y'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ý'''+ ', '+'''y'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''þ'''+ ', '+'''þ'''+')

UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ç'''+ ', '+'''C'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ç'''+ ', '+'''c'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''œ'''+ ', '+'''ce'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Œ'''+ ', '+'''CE'''+')

UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ì'''+ ', '+'''I'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Í'''+ ', '+'''I'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Î'''+ ', '+'''I'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ï'''+ ', '+'''I'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ì'''+ ', '+'''i'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''í'''+ ', '+'''i'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''î'''+ ', '+'''i'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ï'''+ ', '+'''i'''+')

UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ð'''+ ', '+'''D'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Þ'''+ ', '+'''D'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ß'''+ ', '+'''B'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Š'''+ ', '+'''S'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''š'''+ ', '+'''s'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''§'''+ ', '+'''S'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''½'''+ ', '+'''1/2'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''¼'''+ ', '+'''1/4'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''¾'''+ ', '+'''3/4'''+')

UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''©'''+ ', '+'''Copyright '''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''®'''+ ', '+'''Registered trademark  '''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''‰'''+ ', '+'''%'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''¿'''+ ', '+'''?'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''º'''+ ', '+'''0'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''¹'''+ ', '+'''1'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''²'''+ ', '+'''2'''+')
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''³'''+ ', '+'''3'''+')

exec (@sql)
end

go
EXEC dbo.specialcharacterreplacer @tblname = 'insert tablename here', @column_name ='insert columnname  here'  ```

CHill60 24-Feb-16 10:41am
Yuk!

## Solution 8

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[RemoveSpecialSymbols] (@s varchar(256))

RETURNS varchar(256)

BEGIN

IF @s IS NULL

RETURN NULL

declare @p int

set @p = 0

while @p <= 127 begin

if @p < 32 or @p between 33 and 47 or @p between 58 and 64 or @p between 91 and 96 or @p > 122

set @s = replace(@s, char(@p), ' ')

set @p = @p + 1

end

RETURN @s

END