replace special characters in sql

Question

1.00/5 (2 votes)

See more:

SQL-Server

I have string with special characters.so how to
replace special characters in sql like "<[^>]+/\'.{}()#$*@!:;?>."

Posted 10-Sep-12 0:37am

softprga

Updated 2-Jan-20 17:54pm

_Amy

v2

Add a Solution

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

KM Perumal · Answer 1 · 2014-12-17T00:14:00

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

Posted 17-Dec-14 0:14am

KM Perumal

Comments

Member 12331432 4-Apr-17 14:01pm

reererer

_Amy · Answer 2 · 2012-09-10T00:44:00

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

Posted 10-Sep-12 0:44am

_Amy

Comments

__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. :)

Sunil Kumar Pandab · Answer 3 · 2012-09-10T00:58:00

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')

sql_lover · Answer 4 · 2016-02-24T03:37:00

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'''+') 
 
' -- add your special character here inside 
exec (@sql)
end

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

Member 14191904 · Answer 5 · 2020-01-02T17:55:00

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

replace special characters in sql

5 solutions

Solution 5

Solution 1

Solution 3

Solution 6

Solution 8

Add your solution here

Preview 0