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.

  8. 나누미 검증 추천 | 바둑이사이트 현금바둑이 딱 2곳만 추천 가장 안전하고 정직하며 신속한 바둑이사이트를 메이저 중에서도 메이저로 만나실 수 있습니다 한국을 대표하는 바둑이게임 더 이상의 바둑이사이튼 절대 없습니다 먹튀검증사이트 나누미 추천은 정확합니다 바둑이게임.