How to Escape Square Brackets in a LIKE Clause in SQL Server?
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 Beginner;
Step 2: Using a database
Use the below SQL statement to switch the database context to Beginner:
Query:
USE Beginner;
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:
Contact Us