Regular expressions are a powerful tool for pattern matching in SQL Server. They allow you to search for specific patterns within strings, making it easier to retrieve the data you need. In this article, we will explore how to use REGEXP in SQL Server.

Let’s start by creating a sample table:

CREATE TABLE items (
  item_id INT,
  item_description VARCHAR(100)
);

INSERT INTO items VALUES (1, 'Television');
INSERT INTO items VALUES (2, 'Mobile');
INSERT INTO items VALUES (3, 'laptop');
INSERT INTO items VALUES (4, 'Cables');
INSERT INTO items VALUES (5, 'Camera');
INSERT INTO items VALUES (6, 'jewels');
INSERT INTO items VALUES (7, 'shirt');
INSERT INTO items VALUES (8, 'Cup');
INSERT INTO items VALUES (9, 'Pen');
INSERT INTO items VALUES (10, 'Pencil');

Now, let’s explore some examples of using REGEXP in SQL Server:

1. Find item_description that starts with ‘c’

SELECT item_description
FROM items
WHERE item_description LIKE 'c%';

Result:

Item_description
Cables
Camera
Cup

2. Find item_description that ends with ‘s’

SELECT item_description
FROM items
WHERE item_description LIKE '%s';

Result:

Item_description
Cables
jewels

3. Find item_description that starts with ‘c’ or ends with ‘s’

SELECT item_description
FROM items
WHERE item_description LIKE 'c%' OR item_description LIKE '%s';

Result:

Item_description
Cables
Camera
jewels
Cup

4. Find item_description that contains the letter ‘a’

SELECT item_description
FROM items
WHERE item_description LIKE '%a%';

Result:

Item_description
laptop
Cables
Camera

5. Find item_description that contains the letter ‘c’ or ‘p’

SELECT item_description
FROM items
WHERE item_description LIKE '%c%' OR item_description LIKE '%p%';

Result:

Item_description
Cables
Camera
Cup
Pen
Pencil

These examples demonstrate how REGEXP can be used to perform pattern matching in SQL Server. By using regular expressions, you can easily search for specific patterns within your data, making it more efficient to retrieve the information you need.