QUOTE () function in MySQL

QUOTE() :
This function in MySQL is used to return a result that can be used as a properly escaped data value in an SQL statement. The string is returned enclosed by single quotation marks and with each instance of backslash (\), single quote (‘), ASCII NULL, and Control+Z preceded by a backslash. If the argument is NULL, the return value is the word “NULL” without enclosing single quotation marks.

Syntax :

QUOTE(string)

Parameters :
This method accepts one parameter.

• string – The Input String.

Returns :
It returns a string with properly escaped data value in an SQL statement.

Example-1 :
Escaping single quotes in the string ‘geeks”for”geeks’ with the help of QUOTE Function.

SELECT QUOTE('geeks''for''geeks' ) 
AS Escaped_String;

Output :

Escaped_String
‘geeks\’for\’geeks’

Example-2 :
Escaping backslash in the string ‘geeks\for’\geeks’ with the help of QUOTE Function.

SELECT QUOTE('geeks\for\geeks' ) 
AS Escaped_String;

Output :

ESCAPED_STRING
‘geeksforgeeks’

Example-3 :
QUOTE Function can also be used in column data. To demonstrate create a table named Student.

CREATE TABLE Student
(
Student_id INT AUTO_INCREMENT,  
Student_name VARCHAR(100) NOT NULL,
Roll INT NOT NULL,
Department VARCHAR(10) NOT NULL,
PRIMARY KEY(Student_id )
);

Inserting some data to the Student table :

INSERT INTO Student
(Student_name, Roll, Department )
VALUES
('Anik Biswas ', 10100, 'CSE'),
('Bina Mallick', 11000, 'ECE' ),
('Aniket Sharma', 12000, 'IT' ),
('Sayani Samanta', 13000, 'ME'  ),
('Riyanka Shah ', 14000, 'EE' ) ;

So, the Student Table is as follows.

SELECT  * from Student ;

Output :

STUDENT_ID	STUDENT_NAME	ROLL	DEPARTMENT
1	Anik Biswas	10100	CSE
2	Bina Mallick	11000	ECE
3	Aniket Sharma	12000	IT
4	Sayani Samanta	13000	ME
5	Riyanka Shah	14000	EE

Now, we are going to use QUOTE Function on Department column.

SELECT *, QUOTE (Department) FROM Student;

Output :

STUDENT_ID	STUDENT_NAME	ROLL	DEPARTMENT	QUOTE (DEPARTMENT)
1	Anik Biswas	10100	CSE	‘CSE’
2	Bina Mallick	11000	ECE	‘ECE’
3	Aniket Sharma	12000	IT	‘IT’
4	Sayani Samanta	13000	ME	‘ME’
5	Riyanka Shah	14000	EE	‘EE’