MySQL - How to include special characters in a query


When you’re writing a MySQL query, there may be times when you need to include special characters in your statement.

For example, suppose you want to include a quote symbol ' inside your SELECT statement like this:

SELECT 'Hello, I'm Nathan';

The above query will trigger ERROR 1064 because you are putting a quote symbol ' that’s used as a delimiter to the string.

To fix the error, you need to escape the character from being interpreted as the delimiter of a string.

In MySQL, you can escape quote symbols by alternating between the single and double quote symbols.

If you’re using a single quote symbol in your string, use the double quote for the string delimiter as shown below:

SELECT "Hello, I'm Nathan";

-- Output:
-- +-------------------+
-- | Hello, I'm Nathan |
-- +-------------------+
-- | Hello, I'm Nathan |
-- +-------------------+

You can also do it like this:

SELECT 'Hello, I"m Nathan';

-- Output:
-- +-------------------+
-- | Hello, I"m Nathan |
-- +-------------------+
-- | Hello, I"m Nathan |
-- +-------------------+

Alternatively, MySQL also has special character escape sequences as shown below:

  • \0 - An ASCII NUL (0x00) character.
  • \' - A single quote (') character.
  • \" - A double quote (") character.
  • \b - A backspace character.
  • \n - A newline (linefeed) character.
  • \r - A carriage return character.
  • \t - A tab character.
  • \Z - ASCII 26 (Control-Z).
  • \\ - A backslash (\) character.
  • \% - A % character.
  • \_ - A _ character.

By using the above syntax, you can add the characters mentioned without being interpreted by MySQL.

Take a look at the following example:

SELECT 'Hello, I\'m Nathan';

-- Output:
-- +-------------------+
-- | Hello, I'm Nathan |
-- +-------------------+
-- | Hello, I'm Nathan |
-- +-------------------+

To add a newline character, use the \n escape sequence as shown below:

SELECT 'Hello, my name \n is Nathan';

-- Output:
-- +----------------------------+
-- | Hello, my name 
--  is Nathan |
-- +----------------------------+
-- | Hello, my name 
--  is Nathan |
-- +----------------------------+

And that’s how you include special characters in a MySQL SELECT query.

Get 50 MySQL Snippets Book for FREE 🔥

50 MySQL snippets that you can use in various scenarios

Save 1000+ hours of research and 10x your productivity