Solved: Re: Problema de codificação - Qlik Community

Report Inappropriate Content · ‎2016-05-11

Boa noite pessoal, tenho uma tabela que recebo dados de um banco Mysql.

O campo "descricao" está vindo com problema de codificação.

Por exemplo: a frase: "Descrição Procedimento." está como "Descrição Procedimento." Olhei no banco de dados mysql, e a frase está armazenada assim também: "Descrição Procedimento.".

Como posso corrigir este problema?

Clever_Anjos · ‎2016-05-12

Utilize a função HTML_UnEncode

SQL

SELECT

HTML_UnEncode(Descricao) as Descricao,

outros campos

FROM suatabela;

View solution in original post

Clever_Anjos · ‎2016-05-12

Utilize a função HTML_UnEncode

SQL

SELECT

HTML_UnEncode(Descricao) as Descricao,

outros campos

FROM suatabela;

Clever_Anjos · ‎2016-05-12

Mais detalhes em tiplicks!: MySQL function for replacing HTML entities with their actual characters - 'nix, shell, pe...

Report Inappropriate Content · ‎2016-05-12

A função apresentada no link acima estava apresentando um erro para mim. Executei esta função abaixo que encontrei em outro site e deu tudo certo. Mais uma vez obrigado Clever.

Vou deixar a função abaixo para quem precisar.

/*******************************************************************************************************************/

DELIMITER $$

DROP FUNCTION IF EXISTS `nomedoseubanco`.`HTML_UnEncode`$$

CREATE DEFINER=`usuariodobanco`@`localhost` FUNCTION `HTML_UnEncode`(x VARCHAR(255)) RETURNS varchar(255) CHARSET latin1

BEGIN

DECLARE TextString VARCHAR(255) ;

SET TextString = x ;

#quotation mark

IF INSTR( x , '"' )

THEN SET TextString = REPLACE(TextString, '"','"') ;

END IF ;

#apostrophe

IF INSTR( x , ''' )

THEN SET TextString = REPLACE(TextString, ''','"') ;

END IF ;

#ampersand

IF INSTR( x , '&' )

THEN SET TextString = REPLACE(TextString, '&','&') ;

END IF ;

#less-than

IF INSTR( x , '<' )

THEN SET TextString = REPLACE(TextString, '<','<') ;

END IF ;

#greater-than

IF INSTR( x , '>' )

THEN SET TextString = REPLACE(TextString, '>','>') ;

END IF ;

#non-breaking space

IF INSTR( x , ' ' )

THEN SET TextString = REPLACE(TextString, ' ',' ') ;

END IF ;

#inverted exclamation mark

IF INSTR( x , '¡' )

THEN SET TextString = REPLACE(TextString, '¡','¡') ;

END IF ;

#cent

IF INSTR( x , '¢' )

THEN SET TextString = REPLACE(TextString, '¢','¢') ;

END IF ;

#pound

IF INSTR( x , '£' )

THEN SET TextString = REPLACE(TextString, '£','£') ;

END IF ;

#currency

IF INSTR( x , '¤' )

THEN SET TextString = REPLACE(TextString, '¤','¤') ;

END IF ;

#yen

IF INSTR( x , '¥' )

THEN SET TextString = REPLACE(TextString, '¥','¥') ;

END IF ;

#broken vertical bar

IF INSTR( x , '¦' )

THEN SET TextString = REPLACE(TextString, '¦','¦') ;

END IF ;

#section

IF INSTR( x , '§' )

THEN SET TextString = REPLACE(TextString, '§','§') ;

END IF ;

#spacing diaeresis

IF INSTR( x , '¨' )

THEN SET TextString = REPLACE(TextString, '¨','¨') ;

END IF ;

#copyright

IF INSTR( x , '©' )

THEN SET TextString = REPLACE(TextString, '©','©') ;

END IF ;

#feminine ordinal indicator

IF INSTR( x , 'ª' )

THEN SET TextString = REPLACE(TextString, 'ª','ª') ;

END IF ;

#angle quotation mark (left)

IF INSTR( x , '«' )

THEN SET TextString = REPLACE(TextString, '«','«') ;

END IF ;

#negation

IF INSTR( x , '¬' )

THEN SET TextString = REPLACE(TextString, '¬','¬') ;

END IF ;

#soft hyphen

IF INSTR( x , '' )

THEN SET TextString = REPLACE(TextString, '','') ;

END IF ;

#registered trademark

IF INSTR( x , '®' )

THEN SET TextString = REPLACE(TextString, '®','®') ;

END IF ;

#spacing macron

IF INSTR( x , '¯' )

THEN SET TextString = REPLACE(TextString, '¯','¯') ;

END IF ;

#degree

IF INSTR( x , '°' )

THEN SET TextString = REPLACE(TextString, '°','°') ;

END IF ;

#plus-or-minus

IF INSTR( x , '±' )

THEN SET TextString = REPLACE(TextString, '±','±') ;

END IF ;

#superscript 2

IF INSTR( x , '²' )

THEN SET TextString = REPLACE(TextString, '²','²') ;

END IF ;

#superscript 3

IF INSTR( x , '³' )

THEN SET TextString = REPLACE(TextString, '³','³') ;

END IF ;

#spacing acute

IF INSTR( x , '´' )

THEN SET TextString = REPLACE(TextString, '´','´') ;

END IF ;

#micro

IF INSTR( x , 'µ' )

THEN SET TextString = REPLACE(TextString, 'µ','µ') ;

END IF ;

#paragraph

IF INSTR( x , '¶' )

THEN SET TextString = REPLACE(TextString, '¶','¶') ;

END IF ;

#middle dot

IF INSTR( x , '·' )

THEN SET TextString = REPLACE(TextString, '·','·') ;

END IF ;

#spacing cedilla

IF INSTR( x , '¸' )

THEN SET TextString = REPLACE(TextString, '¸','¸') ;

END IF ;

#superscript 1

IF INSTR( x , '¹' )

THEN SET TextString = REPLACE(TextString, '¹','¹') ;

END IF ;

#masculine ordinal indicator

IF INSTR( x , 'º' )

THEN SET TextString = REPLACE(TextString, 'º','º') ;

END IF ;

#angle quotation mark (right)

IF INSTR( x , '»' )

THEN SET TextString = REPLACE(TextString, '»','»') ;

END IF ;

#fraction 1/4

IF INSTR( x , '¼' )

THEN SET TextString = REPLACE(TextString, '¼','¼') ;

END IF ;

#fraction 1/2

IF INSTR( x , '½' )

THEN SET TextString = REPLACE(TextString, '½','½') ;

END IF ;

#fraction 3/4

IF INSTR( x , '¾' )

THEN SET TextString = REPLACE(TextString, '¾','¾') ;

END IF ;

#inverted question mark

IF INSTR( x , '¿' )

THEN SET TextString = REPLACE(TextString, '¿','¿') ;

END IF ;

#multiplication

IF INSTR( x , '×' )

THEN SET TextString = REPLACE(TextString, '×','×') ;

END IF ;

#division

IF INSTR( x , '÷' )

THEN SET TextString = REPLACE(TextString, '÷','÷') ;

END IF ;

#capital a, grave accent

IF INSTR( x , 'À' )

THEN SET TextString = REPLACE(TextString, 'À','À') ;

END IF ;

#capital a, acute accent

IF INSTR( x , 'Á' )

THEN SET TextString = REPLACE(TextString, 'Á','Á') ;

END IF ;

#capital a, circumflex accent

IF INSTR( x , 'Â' )

THEN SET TextString = REPLACE(TextString, 'Â','Â') ;

END IF ;

#capital a, tilde

IF INSTR( x , 'Ã' )

THEN SET TextString = REPLACE(TextString, 'Ã','Ã') ;

END IF ;

#capital a, umlaut mark

IF INSTR( x , 'Ä' )

THEN SET TextString = REPLACE(TextString, 'Ä','Ä') ;

END IF ;

#capital a, ring

IF INSTR( x , 'Å' )

THEN SET TextString = REPLACE(TextString, 'Å','Å') ;

END IF ;

#capital ae

IF INSTR( x , 'Æ' )

THEN SET TextString = REPLACE(TextString, 'Æ','Æ') ;

END IF ;

#capital c, cedilla

IF INSTR( x , 'Ç' )

THEN SET TextString = REPLACE(TextString, 'Ç','Ç') ;

END IF ;

#capital e, grave accent

IF INSTR( x , 'È' )

THEN SET TextString = REPLACE(TextString, 'È','È') ;

END IF ;

#capital e, acute accent

IF INSTR( x , 'É' )

THEN SET TextString = REPLACE(TextString, 'É','É') ;

END IF ;

#capital e, circumflex accent

IF INSTR( x , 'Ê' )

THEN SET TextString = REPLACE(TextString, 'Ê','Ê') ;

END IF ;

#capital e, umlaut mark

IF INSTR( x , 'Ë' )

THEN SET TextString = REPLACE(TextString, 'Ë','Ë') ;

END IF ;

#capital i, grave accent

IF INSTR( x , 'Ì' )

THEN SET TextString = REPLACE(TextString, 'Ì','Ì') ;

END IF ;

#capital i, acute accent

IF INSTR( x , 'Í' )

THEN SET TextString = REPLACE(TextString, 'Í','Í') ;

END IF ;

#capital i, circumflex accent

IF INSTR( x , 'Î' )

THEN SET TextString = REPLACE(TextString, 'Î','Î') ;

END IF ;

#capital i, umlaut mark

IF INSTR( x , 'Ï' )

THEN SET TextString = REPLACE(TextString, 'Ï','Ï') ;

END IF ;

#capital eth, Icelandic

IF INSTR( x , 'Ð' )

THEN SET TextString = REPLACE(TextString, 'Ð','Ð') ;

END IF ;

#capital n, tilde

IF INSTR( x , 'Ñ' )

THEN SET TextString = REPLACE(TextString, 'Ñ','Ñ') ;

END IF ;

#capital o, grave accent

IF INSTR( x , 'Ò' )

THEN SET TextString = REPLACE(TextString, 'Ò','Ò') ;

END IF ;

#capital o, acute accent

IF INSTR( x , 'Ó' )

THEN SET TextString = REPLACE(TextString, 'Ó','Ó') ;

END IF ;

#capital o, circumflex accent

IF INSTR( x , 'Ô' )

THEN SET TextString = REPLACE(TextString, 'Ô','Ô') ;

END IF ;

#capital o, tilde

IF INSTR( x , 'Õ' )

THEN SET TextString = REPLACE(TextString, 'Õ','Õ') ;

END IF ;

#capital o, umlaut mark

IF INSTR( x , 'Ö' )

THEN SET TextString = REPLACE(TextString, 'Ö','Ö') ;

END IF ;

#capital o, slash

IF INSTR( x , 'Ø' )

THEN SET TextString = REPLACE(TextString, 'Ø','Ø') ;

END IF ;

#capital u, grave accent

IF INSTR( x , 'Ù' )

THEN SET TextString = REPLACE(TextString, 'Ù','Ù') ;

END IF ;

#capital u, acute accent

IF INSTR( x , 'Ú' )

THEN SET TextString = REPLACE(TextString, 'Ú','Ú') ;

END IF ;

#capital u, circumflex accent

IF INSTR( x , 'Û' )

THEN SET TextString = REPLACE(TextString, 'Û','Û') ;

END IF ;

#capital u, umlaut mark

IF INSTR( x , 'Ü' )

THEN SET TextString = REPLACE(TextString, 'Ü','Ü') ;

END IF ;

#capital y, acute accent

IF INSTR( x , 'Ý' )

THEN SET TextString = REPLACE(TextString, 'Ý','Ý') ;

END IF ;

#capital THORN, Icelandic

IF INSTR( x , 'Þ' )

THEN SET TextString = REPLACE(TextString, 'Þ','Þ') ;

END IF ;

#small sharp s, German

IF INSTR( x , 'ß' )

THEN SET TextString = REPLACE(TextString, 'ß','ß') ;

END IF ;

#small a, grave accent

IF INSTR( x , 'à' )

THEN SET TextString = REPLACE(TextString, 'à','à') ;

END IF ;

#small a, acute accent

IF INSTR( x , 'á' )

THEN SET TextString = REPLACE(TextString, 'á','á') ;

END IF ;

#small a, circumflex accent

IF INSTR( x , 'â' )

THEN SET TextString = REPLACE(TextString, 'â','â') ;

END IF ;

#small a, tilde

IF INSTR( x , 'ã' )

THEN SET TextString = REPLACE(TextString, 'ã','ã') ;

END IF ;

#small a, umlaut mark

IF INSTR( x , 'ä' )

THEN SET TextString = REPLACE(TextString, 'ä','ä') ;

END IF ;

#small a, ring

IF INSTR( x , 'å' )

THEN SET TextString = REPLACE(TextString, 'å','å') ;

END IF ;

#small ae

IF INSTR( x , 'æ' )

THEN SET TextString = REPLACE(TextString, 'æ','æ') ;

END IF ;

#small c, cedilla

IF INSTR( x , 'ç' )

THEN SET TextString = REPLACE(TextString, 'ç','ç') ;

END IF ;

#small D', D

IF INSTR( x , 'D' ' )

THEN SET TextString = REPLACE(TextString, 'D' ','D') ;

END IF ;

#small e, grave accent

IF INSTR( x , 'è' )

THEN SET TextString = REPLACE(TextString, 'è','è') ;

END IF ;

#small e, acute accent

IF INSTR( x , 'é' )

THEN SET TextString = REPLACE(TextString, 'é','é') ;

END IF ;

#small e, circumflex accent

IF INSTR( x , 'ê' )

THEN SET TextString = REPLACE(TextString, 'ê','ê') ;

END IF ;

#small e, umlaut mark

IF INSTR( x , 'ë' )

THEN SET TextString = REPLACE(TextString, 'ë','ë') ;

END IF ;

#small i, grave accent

IF INSTR( x , 'ì' )

THEN SET TextString = REPLACE(TextString, 'ì','ì') ;

END IF ;

#small i, acute accent

IF INSTR( x , 'í' )

THEN SET TextString = REPLACE(TextString, 'í','í') ;

END IF ;

#small i, circumflex accent

IF INSTR( x , 'î' )

THEN SET TextString = REPLACE(TextString, 'î','î') ;

END IF ;

#small i, umlaut mark

IF INSTR( x , 'ï' )

THEN SET TextString = REPLACE(TextString, 'ï','ï') ;

END IF ;

#small eth, Icelandic

IF INSTR( x , 'ð' )

THEN SET TextString = REPLACE(TextString, 'ð','ð') ;

END IF ;

#small n, tilde

IF INSTR( x , 'ñ' )

THEN SET TextString = REPLACE(TextString, 'ñ','ñ') ;

END IF ;

#small o, grave accent

IF INSTR( x , 'ò' )

THEN SET TextString = REPLACE(TextString, 'ò','ò') ;

END IF ;

#small o, acute accent

IF INSTR( x , 'ó' )

THEN SET TextString = REPLACE(TextString, 'ó','ó') ;

END IF ;

#small o, circumflex accent

IF INSTR( x , 'ô' )

THEN SET TextString = REPLACE(TextString, 'ô','ô') ;

END IF ;

#small o, tilde

IF INSTR( x , 'õ' )

THEN SET TextString = REPLACE(TextString, 'õ','õ') ;

END IF ;

#small o, umlaut mark

IF INSTR( x , 'ö' )

THEN SET TextString = REPLACE(TextString, 'ö','ö') ;

END IF ;

#small o, slash

IF INSTR( x , 'ø' )

THEN SET TextString = REPLACE(TextString, 'ø','ø') ;

END IF ;

#small u, grave accent

IF INSTR( x , 'ù' )

THEN SET TextString = REPLACE(TextString, 'ù','ù') ;

END IF ;

#small u, acute accent

IF INSTR( x , 'ú' )

THEN SET TextString = REPLACE(TextString, 'ú','ú') ;

END IF ;

#small u, circumflex accent

IF INSTR( x , 'û' )

THEN SET TextString = REPLACE(TextString, 'û','û') ;

END IF ;

#small u, umlaut mark

IF INSTR( x , 'ü' )

THEN SET TextString = REPLACE(TextString, 'ü','ü') ;

END IF ;

#small y, acute accent

IF INSTR( x , 'ý' )

THEN SET TextString = REPLACE(TextString, 'ý','ý') ;

END IF ;

#small thorn, Icelandic

IF INSTR( x , 'þ' )

THEN SET TextString = REPLACE(TextString, 'þ','þ') ;

END IF ;

#small y, umlaut mark

IF INSTR( x , 'ÿ' )

THEN SET TextString = REPLACE(TextString, 'ÿ','ÿ') ;

END IF ;

RETURN TextString ;

END$$

DELIMITER ;