MySQL - How to include special characters in a query

Posted on Dec 19, 2021

Learn how to escape special characters in a MySQL SELECT statement

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.

Level up your programming skills

I'm sending out an occasional email with the latest programming tutorials. Drop your email in the box below and I'll send new stuff straight into your inbox!

No spam. Unsubscribe anytime.