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.