How to replace special character in sql table and replace by desired character dynamically?

Question

eg

'è' by 'e'
'á'by a
'¾' by '3/4'
ó by 'o'
'ñ' by'n'
'á' by 'a'

Not working ...but the result is above I want.

WHILE EXSITS (SELECT id from #tmp_dirtytable)
BEGIN
-- Step 1: Read the required data row from the temp table
DECLARE @id INT
DECLARE @special_character VARCHAR(100)
DECLARE @special_character_to_replace_with VARCHAR(100)
SELECT @id = id, @special_character = special_character, @special_char_bal_bla = something from #tmp_dirtytable


-- Step 2: Write the replace logic here


-- Step 3: Delete the row that we have processed from the temp table
DELETE FROM #tmp_dirtytable where id = @id

END

To get better attention, tag dbms used. (That stored procedure isn't ANSI SQL.) — jarlh, Feb 08 '16 at 07:53
In [this answer](http://stackoverflow.com/a/32048968/5089204) you'll find a way, where a string is split into single letters, each of which can be replaced with whatever you like and is concatenated for string output at the end. All this is done in one single ad-hoc statement. — Shnugo, Feb 25 '16 at 17:45

Muhammad Azim · Answer 1 · 2016-02-08T09:54:48.447

0

If you are using SQL Server, this query will help you

Create proc specialcharacterreplacer
@tblname varchar(1000),
@column_name varchar(1000)
as
begin
Declare @query varchar(max)
SET @query = 'UPDATE '+@tblname +' SET ' +@column_name + ' = dbo.ReplaceSpecialCharacter('+@column_name+')'
Exec(@query)
end

CREATE FUNCTION ReplaceSpecialCharacter
(
@Input varchar(max)
)
RETURNS varchar(Max)
AS
BEGIN
Declare @Result varchar(Max)
SET @Result = Replace(@Input,'è','e')
SET @Result = Replace(@Result,'á','a')
SET @Result = Replace(@Result,'¾','3/4')
SET @Result = Replace(@Result,'ó','o')
SET @Result = Replace(@Result,'ä','a')
SET @Result = Replace(@Result,'ñ','n')
SET @Result = Replace(@Result,'á','a')
Return @Result
END
GO

edited Feb 08 '16 at 09:54

answered Feb 08 '16 at 08:50

Muhammad Azim

329
1
2
14

Muhammad Azim .thanx for the help but it works only for a ..i want it dynamically for all special characters... how to do it ? – awakenedsoul Feb 08 '16 at 09:21
I just update my answer. You may use custom Scalar valued function to perform your replace operation. – Muhammad Azim Feb 08 '16 at 09:58
I have already done that way.. Its still not dynamic ... I have to process all the special characters available... – awakenedsoul Feb 08 '16 at 10:10
You may delete all special character but if you want to replace all special character you need to manually map special character to regular character. – Muhammad Azim Feb 08 '16 at 11:19
i have done that but now i want to go check through all the columns of a table dynamically n automatically... plz help EXEC dbo.specialcharacterreplacer tblname = 'dirtytable', column_name = 'all columns of a table' – awakenedsoul Feb 09 '16 at 10:31

score 0 · Answer 2 · edited Feb 25 '16 at 17:42

WHILE EXSITS (SELECT id from #tmp_dirtytable)
BEGIN
-- Step 1: Read the required data row from the temp table
DECLARE @id INT
DECLARE @special_character VARCHAR(100)
DECLARE @special_character_to_replace_with VARCHAR(100)
SELECT @id = id, @special_character = special_character, @special_char_bal_bla = something from #tmp_dirtytable


-- Step 2: Write the replace logic here


-- Step 3: Delete the row that we have processed from the temp table
DELETE FROM #tmp_dirtytable where id = @id

END

score 0 · Answer 3 · answered Feb 24 '16 at 15:00

    -- Replacing special character by normal character eg ó by o,ê by e etc

  create proc dbo.specialcharacterreplacer

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

as
begin


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'

How to replace special character in sql table and replace by desired character dynamically?

3 Answers3