SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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  


I have total of 11 years of IT experience with Application development, Database Development and Database Administration. I have worked with different version of SQL server from 7.0 to 2008.Started my carrier as VB ,VC++ and database developer in a banking sector for implementing their core banking solution. Currently working as Database Administrator with wide knowledge in performance tuning, high availability solution, troubleshooting and server monitoring. This blog is my humble attempt to share my knowledge and what I learned from my day to day work.


Leave a comment on the original post [www.practicalsqldba.com, opens in a new window]

Loading comments...