Mysql select without special characters

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.