How to show and search for hidden characters (strings, etc.) in SQL

Question

I have a large MySQL database with a lot of text (for example, "[new line] Normal") that I want to find and replace. However, I cannot find it with any of the following searches, because I'm not sure which characters are used where the [new line] occurs - hence my question.

SELECT * FROM table WHERE field LIKE "%<!--[if gte mso 9]><xml>\nNormal%";

SELECT * FROM table WHERE field LIKE "%<!--[if gte mso 9]><xml>\rNormal%";

SELECT * FROM table WHERE field LIKE "%<!--[if gte mso 9]><xml>\n\rNormal%";

SELECT * FROM table WHERE field LIKE "%<!--[if gte mso 9]><xml>\r\nNormal%";

Thanks for any help you can give ...

Answer 1

You can have line endings like CR / LF ie # 13 # 10 (Dos / Windows convention) or just LF - # 10 (Unix convention)

Make sure that any occurrence of the first is converted to the second:

UPDATE TABLE
SET field = REPLACE( field, CONCAT(CHAR(13),CHAR(10)), CHAR(10) )

To be safe, you can also:

UPDATE TABLE
SET field = REPLACE( field, CHAR(13), CHAR(10) )

" " # 10 ( )

, - :

UPDATE TABLE
SET field = REPLACE( field, CHAR(10), '[LF]' )

Answer 2

, .

: CHAR (10)/CHAR (13), , ​​, :

SELECT * FROM table WHERE field LIKE "%<!--[if gte mso 9]><xml>
Normal%";

, :

SELECT * FROM table WHERE field LIKE CONCAT("%<!--[if gte mso 9]><xml>", CHAR(13), CHAR(10), "Normal%");

Answer 3

"" , , UNIX, Ctrl + v. Ctrl + v, , , , . , , , , - , .

It will not work if you are on a Windows system, but this tip may help someone who has a similar problem and does not have access to the CHAR () functions.