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 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:
Last Updated :
15 Oct, 2021
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...