Home Ask Login Register

Developers Planet

Your answer is one click away!

awakenedsoul February 2016

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

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

Answers


Muhammad Azim February 2016

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


awakenedsoul February 2016

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


sql_lover February 2016

    -- 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 ' + @tblnam 

Post Status

Asked in February 2016
Viewed 2,332 times
Voted 12
Answered 3 times

Search




Leave an answer


Quote of the day: live life