:::: MENU ::::

T-SQL wild card characters for use with like operator

  • Apr 26 / 2011
  • 1
dbDigger, T-SQL Interview Questions, T-SQL Tips and Tricks

T-SQL wild card characters for use with like operator

SQL Server provides four wild card characters to use with like operator. These are % , _ , [], [^]. These wild card characters provide very powerful capabilities to operate on string data. At morning i was required to operate on a column with strings containing digits. There were few values where alphabets were also in the value. I was required to separate the values which also contain  alphabets along with digits.
This can be done by using above mentioned wild card characters in T-SQL. Following is full example

 -- Create Table in Specified database  
 IF EXISTS (SELECT * FROM sys.objects  
 WHERE name = N'StringsTest' AND [type] = 'U')  
 DROP TABLE StringsTest  
 CREATE TABLE StringsTest (StringValue varchar(230))  
 GO  
 -- Populate the table  
 INSERT INTO StringsTest  
 SELECT '120-35-AQS' UNION ALL  
 SELECT '180-35-ASP' UNION ALL  
 SELECT '15335'    UNION ALL  
 SELECT '158-895'  UNION ALL  
 SELECT '158895'    UNION ALL  
 SELECT 'ASDBFC'    UNION ALL    
 SELECT 'ACVFRHB'  UNION ALL    
 SELECT 'ABC-158-35'    
 GO  
 -- To get values where alphabets are present  
 SELECT * FROM StringsTest   
 WHERE StringValue LIKE '%[A-Z]%'  
 -- To get values where digits are present  
 SELECT * FROM StringsTest   
 WHERE StringValue LIKE '%[^A-Z]%'  
 -- To get values where Q,R or S follows -A  
 SELECT * FROM StringsTest   
 WHERE StringValue LIKE '%-_[Q-S]%'  

In the same way you can build many more regular expressions as required in a specific scenario.

Consult us to explore the Databases. Contact us