How to show and search for hidden characters (line feeds, etc) in SQL

Question

I have a large MySQL database with lots of text (like '[new line]Normal') that I want to find & replace. However, I can't find it with any of the following searches, because I'm not sure what 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 might have end of lines as CR/LF i.e. #13#10 (Dos/Windows convention) or just LF - #10 (Unix convention)

Make sure any occurrence of the first is converted into the second:

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

just to be safe you can also do:

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

Now any "End of line" character will be #10 (Line Feed)

If you wish, you can do something like:

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

Answer 2

You shouldn't need to update any rows to use this query.

You actually have two options - you can use the CHAR(10)/CHAR(13) approach, as others have noted, or you can actually send a query with a literal newline in it, like so:

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

To run this query without needing to use literal newlines:

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

Answer 3

In a more general sense, "hidden" characters can be typed easily on the terminal, at least on UNIX-based systems, using Ctrl+v. If you hit Ctrl+v, the next character you type will be printed literally to the terminal instead of interpreted by any of the intervening systems. Using this, you can just type into whatever you're using and search for newlines, tabs, or anything else the terminal eats.

Won't work if you're on a Windows system, but this tip might help someone out who has a similar problem and doesn't have access to CHAR() functions.