Thursday, 13 September 2012

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.

If you liked this post, do like my page on FaceBook  


  1. Replies
    1. London has many natural strengths. Beyond its recognition as a international economic center the metropolis is the clean worldwide leader especially regions which include forex shopping for and selling bond buying and selling circulate border financial organization lending.

  2. I want to pattern match "return" and space and "numeric value"
    like return 99,return 200.
    I tried like '%return_[0-9]%'

  3. Very much useful.Many Many thanks!!!!

  4. شركة نقل عفش بالمدينة المنورة شركة نقل عفش بجدة شركة نقل عفش بالرياض شركة نقل عفش بالدمام



  7. This comment has been removed by the author.