QUOTENAME Function in MS SQL Server and MySQL

MSSQL

The QUOTENAME function in MSSQL returns a passed string with delimiter added to make it a valid SQL Server identifier

Syntax

QUOTENAME ( 'character_string' [ , 'quote_character' ] )   

Example

select QUOTENAME('My Column')
-----------------------
[My Column]

select QUOTENAME('My[] Column')
------------------
[My[]] Column]

Notice that the right bracket in the string  'My[] Column' is doubled to indicate an escape character.

The QUOTENAME function is useful if the object names contains spaces or reserved words

MySQL

There is no similar function available in MySQL. But you can create a function in MySQL to mimic this functionality.

DELIMITER //
CREATE FUNCTION QUOTENAME (str varchar(100)) returns varchar(102)
BEGIN
  DECLARE retval varchar(102); 
  DECLARE delimiter char(1);
  set delimiter='`';
  set retval=CONCAT (delimiter, REPLACE(str, delimiter, CONCAT(delimiter,delimiter)), delimiter);
  return (retval);
END;  //
DELIMITER ;

Now you can use this function just as in MSSQL as shown below

mysql> select quotename('My Column');
+------------------------+
| quotename('My Column') |
+------------------------+
| `My Column` |
+------------------------+
1 row in set (0.00 sec)

mysql>

mysql> select quotename('My` Column');
+-------------------------+
| quotename('My` Column') |
+-------------------------+
| `My`` Column` |
+-------------------------+
1 row in set (0.00 sec)

mysql>

Back to Converting Functions from MSSQL to MySQL