SQL Wildcard Characters with Example

We can use SQL Wildcard character in SQL Query to find out particular result set from database table With 'LIKE' Operator.Following are the list of SQL Wildcard characters.

Wildcard

To understand it better lets create 1 dummy table in database.

  1. /****** Object: Table [dbo].[Register] Script Date: 30-11-2015 18:26:51 ******/    
  2. SET    
  3. ANSI_NULLS ON GO    
  4. SET    
  5. QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Register](    
  6. [RegId] [bigintNOT NULL,    
  7. [FirstName] [nvarchar](100) NULL,    
  8. [LastName] [nvarchar](100) NULL,    
  9. [Email] [nvarchar](100) NULL,    
  10. [Mobile] [nvarchar](15) NULL CONSTRAINT [PK_Register] PRIMARY KEY CLUSTERED ([RegId] ASCWITH (    
  11. PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,    
  12. IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,    
  13. ALLOW_PAGE_LOCKS = ON    
  14. ON [PRIMARY]    
  15. ON [PRIMARY] GO    
Insert dummy records in table:
  1. INSERT INTO[dbo].[Register]([RegId], [FirstName], [LastName], [Email], [Mobile])  
  2. VALUES(1, 'Ankur''Mistry''[email protected]''9321453xxx')  
  3. INSERT INTO[dbo].[Register]([RegId], [FirstName], [LastName], [Email], [Mobile])  
  4. VALUES(2, 'Rahul''Lad''[email protected]''9321453xxx')  
  5. INSERT INTO[dbo].[Register]([RegId], [FirstName], [LastName], [Email], [Mobile])  
  6. VALUES(3, 'Nikhil''Rajput''[email protected]''9321453xxx')  
  7. INSERT INTO[dbo].[Register]([RegId], [FirstName], [LastName], [Email], [Mobile])  
  8. VALUES(4, 'Bhal''Mithbavkar''[email protected]''9321453xxx')  
  9. INSERT INTO[dbo].[Register]([RegId], [FirstName], [LastName], [Email], [Mobile])  
  10. VALUES(5, 'Rushabh''Parekh''[email protected]''9321453xxx')  
  11. INSERT INTO[dbo].[Register]([RegId], [FirstName], [LastName], [Email], [Mobile])  
  12. VALUES(6, 'Chaitali''Shah''[email protected]''9321453xxx')  
I have records as per below screen.

SQL Wildcard characters

Query with SQL Wildcard '%'

records

Query 1: Will return the records starting with 'an'.

Query 2: Will return the records which containing string 'ha'.

Query with SQL Wildcard '_'

Query with SQL Wildcard

Query 1: will return result set start with any character, followed by 'ushabh'.

Query 2: will return result set start with any character followed by ah and then again any character followed by 'l'.

Query with SQL Wildcard []'

Run

Query 1: Will check the firstname starting with 'a' 'r' 'c'.

Query 2: Will check firstname range 'a' 'b' 'c'.

Query with SQL Wildcard '[^]' or '[!]'

table

Query 1: Will check the firstname NOT starting with 'r' 'c' ( LIKE [^]).

Query 2: Will check the firstname NOT starting with 'r' 'c' (NOT LIKE [!]).

Please check attached file for table and SQL query scripts.

Ebook Download
View all
Learn
View all