SQL Server: String Pattern Matching

SQL Server: String Pattern Matching

It is common scenario that, we might need to extract the data from the SQL server based on some pattern. For example extract all customers information who has a valid PAN card number (XXXXX0000X). SQL server is not very powerful in pattern matching.We can easily implement simple pattern matching but for complicated one we might need to used Regular Expression using CLR integration. In this post let us discuss about the possibilities of pattern matching using SQL server syntax.

To fetch all customers who has valid PAN card number (5  Alphabet 4 numeric 1 Alphabet), we can use the following query.

SELECT * FROM customers 
WHERE Pancard LIKE '[A-Z][A-Z][A-Z][A-Z][A-Z][0-9][0-9][0-9][0-9][A-Z]'

To fetch all customer, whose postal code does not have any alphabet.


SELECT * FROM customers 
WHERE PostalCode NOT LIKE '%[A-Z]%'

To fetch all customer, whose postal code is alpha numeric 

SELECT * FROM customers 
WHERE PostalCode LIKE '%[A-Z]%'  AND PostalCode LIKE   '%[0-9]%'  

To fetch all customer, whose first character of postal code is not vowels

SELECT * FROM customers 
WHERE PostalCode LIKE '[^aeiou]%'  

To fetch all customer, whose has postal code does not contain special characters  @ ,# ,$ and  %

SELECT * FROM customers 
WHERE PostalCode NOT LIKE '%[@#$%]%'  

To fetch all customers, whose postal code contain the character '%'.  We have to use escape character as '%' used for wild card search.

SELECT * FROM customers 
WHERE PostalCode LIKE  '%@%%'  ESCAPE  '@'

To fetch all customers, whose postal code starts with anything but second character is A to D

SELECT * FROM customers 
WHERE PostalCode LIKE  '_[A-D]%'

These are the common pattern search option available in SQL server . We can mix and match this to make more complicated pattern search.