Interview Question of the Week #029 – Difference Between CHARINDEX vs PATINDEX

Question: What is the difference between CHARINDEX vs PATINDEX?

Answer: We can use either CHARINDEX or PATINDEX to search in a TEXT field in SQL SERVER. The CHARINDEX and PATINDEX functions return the starting position of a pattern you specify.

regular expressions regex Interview Question of the Week #029   Difference Between CHARINDEX vs PATINDEX

Both functions take two arguments. With PATINDEX, you must include percent signs before and after the pattern, unless you are looking for the pattern as the first (omit the first %) or last (omit the last %) characters in a column. For CHARINDEX, the pattern cannot include wildcard characters. The second argument is a character expression, usually a column name, in which Adaptive Server searches for the specified pattern.

Example of CHARINDEX:
USE AdventureWorks2014;
GO
SELECT CHARINDEX('important', DocumentSummary),
DocumentSummary
FROM Production.Document
WHERE Title = 'Repair and Service Guidelines';
GO

Examples of PATINDEX:
USE AdventureWorks2014;
GO
SELECT PATINDEX('%imp_rtant%', DocumentSummary),
DocumentSummary
FROM Production.Document
WHERE Title = 'Repair and Service Guidelines';
GO

Summary:
PATINDEX is CHARINDEX + WildCard Search. Use either of them is depending your business need.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

MySQL – Pattern Matching Comparison Using Regular Expressions with REGEXP

regex MySQL   Pattern Matching Comparison Using Regular Expressions with REGEXPMySQL supports pattern matching comparison using regular expressions with REGEXP keyword. In this post we will see how to use REGEXP in MySQL. We can use the patterns ^, $, | and sqaure braces to effectively match the values.

Let us create the following 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');

1 Find out item_description that starts with c
SELECT item_description FROM items
WHERE item_description regexp '^c';

Result :

Item_description
 Cables
 Camera
 Cup

2 Find out item_description that ends with s
SELECT item_description FROM items
WHERE item_description regexp 's$';

Result :

Item_description
 Cables
 jewels

3 Find out item_description that starts with c or ends with s
SELECT item_description FROM items
WHERE item_description regexp '^c|s$';

Result :

Item_description
 Cables
 Camera
 jewels
 Cup

4 Find out item_description that contains the alphabet a
SELECT item_description FROM items
WHERE item_description regexp '[a]';

Result :

Item_description
 laptop
 Cables
 Camera

5 Find out item_description that contains the alphabet c or p

SELECT item_description FROM items
WHERE item_description regexp '^[cp]';

Result :

Item_description
 Cables
 Camera
 Cup
 Pen
 Pencil

Reference: Pinal Dave (http://blog.sqlauthority.com)