Here we will see, how to escape square brackets in a LIKE clause. LIKE clause used for pattern matching in SQL using wildcard operators like %, ^, [], etc. If we try to filter the record using a LIKE clause with a string consisting of square brackets, we will not get the expected results.

For example:

For a string value Romy[R]kumari in a table. If we try to apply LIKE ‘Romy[R]%’ to the select statement, it will not return anything.

Explanation:

Square brackets[], is among the wildcard operators used in SQL with the LIKE clause. It is used to match any single character within the specified range like ([b-h]) or set ([ghijk]).

We can escape square brackets using two methods:

• Escape using one more square bracket
• Escape using Escape character

Step 1: Create a database

The database can be created using CREATE command.

Query:

CREATE DATABASE geeks;

Step 2: Using a database

Use the below SQL statement to switch the database context to geeks:

Query:

USE geeks;

Step 3:  Table definition

We have the following demo_table in our geek’s database.

Query:

CREATE TABLE demo_table(
FIRSTNAME VARCHAR(20), LASTNAME VARCHAR(20),
EMPCODE VARCHAR(20));

Step 4: Inserting data into the table

Query:

INSERT INTO demo_table VALUES
('ROMY', 'Kumari', 'ROMY[78]KUM'),
('Rinkle', 'Arora', 'RINKLE[78}ARO'),
('Nikhil', 'Kalra','NIKHIL[90]Kal'),
('Pushkar', 'Jha', 'PUSHKAR[91]JHA'),
('Sujata', 'jha', 'SUJATA[98]JHA'),
('Roshini', 'Kumari','ROSHINI[78]');

Step 5:  For a view a table data

Query:

SELECT * FROM demo_table;

Output:

Method 1: using extra bracket

Syntax:

For matching ‘ROMY[78]’ 

SELECT *  
  FROM table_name 
  WHERE column_name LIKE 'ROMY[[]78]%'

Query: To match EMPCODE having [78] 

SELECT *  
  FROM demo_table  
  WHERE EMPCODE LIKE '%[[]78]%'

Output:

Method 2: using an escape character

In this method, we have to specify the character with the ESCAPE keyword after the LIKE clause.

Syntax:

For matching ‘ROMY[78]’ 

SELECT *  
  FROM table_name  
  WHERE column_name LIKE 'ROMY\[78]%' ESCAPE '\';

Query:

SELECT *  
  FROM demo_table
  WHERE EMPCODE LIKE '%\[78]%' ESCAPE '\';

Output: