{"id":1259,"date":"2023-07-19T15:43:19","date_gmt":"2023-07-19T18:43:19","guid":{"rendered":"https:\/\/marcomapa.com\/artigos\/?p=1259"},"modified":"2023-07-19T15:43:19","modified_gmt":"2023-07-19T18:43:19","slug":"tratando-caracteres-especiais-no-mysql","status":"publish","type":"post","link":"https:\/\/marcomapa.com\/artigos\/tratando-caracteres-especiais-no-mysql\/","title":{"rendered":"Tratando Caracteres Especiais no Mysql"},"content":{"rendered":"\n

Caracteres especiais em bancos de dados podem ser um desafio, especialmente ao realizar pesquisas e consultas em dados que cont\u00eam acentos, cedilhas e outros caracteres n\u00e3o-ASCII. Neste artigo, aprenderemos uma pr\u00e1tica fun\u00e7\u00e3o para remover tratar caracteres especiais, tornando as opera\u00e7\u00f5es de busca e filtragem mais precisas e eficientes.<\/p>\n\n\n\n

<\/p>\n\n\n\n

Abaixo a fun\u00e7\u00e3o:<\/p>\n\n\n\n

DELIMITER \/\/

CREATE FUNCTION remove_accent(
String VARCHAR(255)
)
RETURNS VARCHAR(255)
BEGIN
\/****************************************************************************************************************\/
\/** RETIRA ACENTUA\u00c7\u00c3O DAS VOGAIS **\/
\/****************************************************************************************************************\/
SET String = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(String, '\u00e1', 'a'), '\u00e0', 'a'), '\u00e2', 'a'), '\u00e3', 'a'), '\u00e4', 'a');
SET String = REPLACE(REPLACE(REPLACE(REPLACE(String, '\u00e9', 'e'), '\u00e8', 'e'), '\u00ea', 'e'), '\u00eb', 'e');
SET String = REPLACE(REPLACE(REPLACE(REPLACE(String, '\u00ed', 'i'), '\u00ec', 'i'), '\u00ee', 'i'), '\u00ef', 'i');
SET String = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(String, '\u00f3', 'o'), '\u00f2', 'o'), '\u00f4', 'o'), '\u00f5', 'o'), '\u00f6', 'o');
SET String = REPLACE(REPLACE(REPLACE(REPLACE(String, '\u00fa', 'u'), '\u00f9', 'u'), '\u00fb', 'u'), '\u00fc', 'u');

\/****************************************************************************************************************\/
\/** RETIRA ACENTUA\u00c7\u00c3O DAS CONSOANTES **\/
\/****************************************************************************************************************\/
SET String = REPLACE(String, '\u00fd', 'y');
SET String = REPLACE(String, '\u00f1', 'n');
SET String = REPLACE(String, '\u00e7', 'c');

\/****************************************************************************************************************\/
\/** SUBSTITUI ESPA\u00c7OS POR UNDERLINE (_) **\/
\/****************************************************************************************************************\/
SET String = REPLACE(String, ' ', '_');

RETURN String;
END \/\/

DELIMITER ;


Para utiliz\u00e1-la basta o comando:<\/p>\n\n\n\n

SELECT remove_accent('o c\u00e3o \u00e9 bravo');<\/code><\/pre>\n\n\n\n
E teremos o resultado tratado.
<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n

<\/p>\n","protected":false},"excerpt":{"rendered":"
Caracteres especiais em bancos de dados podem ser um desafio, especialmente ao realizar pesquisas e consultas em dados que cont\u00eam<\/p>\n","protected":false},"author":1,"featured_media":1262,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[32],"tags":[],"class_list":["post-1259","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/marcomapa.com\/artigos\/wp-json\/wp\/v2\/posts\/1259","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/marcomapa.com\/artigos\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/marcomapa.com\/artigos\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/marcomapa.com\/artigos\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/marcomapa.com\/artigos\/wp-json\/wp\/v2\/comments?post=1259"}],"version-history":[{"count":1,"href":"https:\/\/marcomapa.com\/artigos\/wp-json\/wp\/v2\/posts\/1259\/revisions"}],"predecessor-version":[{"id":1261,"href":"https:\/\/marcomapa.com\/artigos\/wp-json\/wp\/v2\/posts\/1259\/revisions\/1261"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/marcomapa.com\/artigos\/wp-json\/wp\/v2\/media\/1262"}],"wp:attachment":[{"href":"https:\/\/marcomapa.com\/artigos\/wp-json\/wp\/v2\/media?parent=1259"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/marcomapa.com\/artigos\/wp-json\/wp\/v2\/categories?post=1259"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/marcomapa.com\/artigos\/wp-json\/wp\/v2\/tags?post=1259"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}