13,141,747 members (59,443 online)
Rate this:
See more:
I have string with special characters.so how to
replace special characters in sql like "<[^>]+/\'.{}()#\$*@!:;?>."
Posted 10-Sep-12 0:37am
Updated 24-Feb-16 3:37am
_Amy57.9K
v2

Rate this:

## Solution 5

```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
Rate this:

## Solution 1

You can use REPLACE[^] funcation of sql server to solve the problem. Try this:
```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. :)
Rate this:

Rate this:

## 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
Rate this:

## 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!

Top Experts
Last 24hrsThis month
 OriginalGriff 215 Jochen Arndt 185 Graeme_Grant 170 ppolymorphe 123 CPallini 100
 OriginalGriff 5,092 Graeme_Grant 5,028 ppolymorphe 1,834 Jochen Arndt 1,707 CPallini 1,500