How to see hidden or escape characters inside a varchar column?

Using MySQL 5.6.

I have a varchar column that stores addresses as text.

When they come through to my application into a text area box, they are spaced appropriately, like

123 Fake St.
Some City, NJ 12345

I tried inserting a new address directly into the database but it doesn’t work correctly inside the GUI, it all came in as one line with no line break, like 123 Fake Str. Some City, NJ 12345

I just want to be able to examine one of the addresses that does work to see how it was done (this was all done before my time at this company) so I can replicate it for the new address, as I have tried n, rn to no success.

However, when I do SELECT address FROM myTable I just see it as plain text, no escaped characters. How can I view the “raw” form of the address so that I can see how the line breaks were implemented?

Answer

You could try copying the value with the letter before and after into the hex function to see which character is being used. In this example in DBfiddle we see that the newline is 0A which is char(11).
Without having your character we can’t examine it!

SELECT HEX('
') newline

| newline |
| :------ |
| 0A      |

db<>fiddle here