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