Special characters in database

Question

I'm running into an error exporting my database data into an excel spreadsheet:

""\v', hexadecimal value 0x0B, is an invalid character.

It looks like this is the symbol for male (♂️), and I'm not even seeing this in my database query. Is there a way to search for this hexadecimal value within an existing query?

https://stackoverflow.com/questions/1744334/the-quest-for-0x0b and https://stackoverflow.com/questions/11794487/sql-server-how-to-find-hexadecimal-character-in-a-table might help with the scan. — TEEKAY, Mar 08 '18 at 17:59
@TEEKAY The second link was what I needed! I had narrowed my error down to 15 rows, then when I ran the query, it selected 6 rows. Found my culprit! — LOL. NO., Mar 08 '18 at 18:02

Dan Guzman · Accepted Answer · 2018-03-08T18:28:55.787

1

ASCII 0x0B is a vertical. To find values containing the character, use CHAR and LIKE:

WHERE YourColumn LIKE '%' + CHAR(11) + '%'

edited Mar 08 '18 at 18:28

answered Mar 08 '18 at 17:54

Dan Guzman

43,250
3
46
71

If it is 0B (zero Beta) then... WHERE YourColumn LIKE '%' + CHAR(11) + '% – Sql Surfer Mar 08 '18 at 18:03
It's actually 0x0B and not 0x08, but the general query is definitely what I needed. Was able to use CHAR(0x0B) like a charm. – LOL. NO. Mar 08 '18 at 18:03
@LOL.NO., time to clean my glasses. I updated my answer. – Dan Guzman Mar 08 '18 at 18:29

Special characters in database

1 Answers1