In MySQL, we can use the sys.quote_identifier() function to quote a string to produce a result that can be used as an identifier in a SQL statement.

The string is returned enclosed by backticks (`), with each instance of a backtick doubled.

This function can be handy when a value to be used as an identifier is a reserved word or contains backtick characters.

Syntax

The syntax goes like this:

sys.quote_identifier(in_identifier)

Where in_identifier is the identifier to quote.

Example

Here’s an example to demonstrate:

SELECT sys.quote_identifier( 'Dog' );

Result:

`Dog`

Existing Backticks are Doubled

If the string already contains a backtick, it’s doubled:

SELECT sys.quote_identifier( 'Identifier with `backticks`' );

Result:

`Identifier with ``backticks```

So the whole string is surrounded by backticks, and any existing backticks are doubled.

Null Argument

If the argument is NULL, then NULL is returned without any backticks:

SELECT sys.quote_identifier( NULL );

Result:

NULL

Passing the Wrong Number of Arguments

Passing the wrong number of arguments results in an error:

SELECT sys.quote_identifier( 'Name', 'Varchar' );

Result:

ERROR 1318 (42000): Incorrect number of arguments for FUNCTION sys.quote_identifier; expected 1, got 2

We get the same error if we omit the argument altogether:

SELECT sys.quote_identifier( );

Result:

ERROR 1318 (42000): Incorrect number of arguments for FUNCTION sys.quote_identifier; expected 1, got 0