SQL SERVER Topic

Wildcards IN SQL
A wildcard character can be used to substitute for any other character(s) in a string. It is use as SQL LIKE operator.

The chart of Wildcards:-

S.No. Wildcard About Wildcard
1. % A substitute for zero or more characters
2. _ A substitute for a single character
3. [charlist] Sets and ranges of characters to match
4. [!charlist] or [^charlist] Matches only a character NOT specified within the brackets


Example :-First of all we create the table named is tblStudentRecord and insert some records as shown in below. 

  

CREATE TABLE tblStudentRecord
(
Name varchar(100), Rollno int primary key, Branch varchar(30),Email varchar(100),Address varchar(200)
)


Insert some records :-

INSERT INTO tblStudentRecord VALUES('Santosh kumar singh',1001,'Information Technology','sa@gmail.com','A-3033')
INSERT INTO tblStudentRecord VALUES('Er. Reena Tanti',1002,'ECE','err@gmail.com','T-387, Gaya')
INSERT INTO tblStudentRecord VALUES('Chandan kumar',1003,'CSE','chn@gmail.com','Gaya')
INSERT INTO tblStudentRecord VALUES('Nand kishor kumar',1004,'BCA','nk@gmail.com','A-3033,Darbhanga')
INSERT INTO tblStudentRecord VALUES('Abhinav kumar',1005,'MCA','ab@gmail.com','H-837')
INSERT INTO tblStudentRecord VALUES('Santosh kumar singh',1006,'CSE','santosh@gmail.com','A-3031')
INSERT INTO tblStudentRecord VALUES('Neha sharma',1007,'MCA','h@gmail.com','D-34,Patna')
INSERT INTO tblStudentRecord VALUES('Pooja',1008,'ECE','pr@gmail.com','A-90, Jaipur, Rajashthan')
INSERT INTO tblStudentRecord VALUES('Jaya Laxmi',1009,'CSE','jl@gmail.com','Kerla')
INSERT INTO tblStudentRecord VALUES('Pakaj Kumar',1010,'BCA','pk@gmail.com','A-30,Darbhanga,Bihar')
INSERT INTO tblStudentRecord VALUES('Abhinav kumar',1011,'ME','ab1@gmail.com','K-837')
INSERT INTO tblStudentRecord VALUES('Suneeta',1012,'MCA','su@gmail.com','UP')
INSERT INTO tblStudentRecord VALUES('Suman Kumar',1013,'IT','sk@gmail.com','Block-T,Darbhanga')
INSERT INTO tblStudentRecord VALUES('Ajay kumar',1014,'ECE','aj@gmail.com','D-89')




1. % Wildcard in SQL:-The following SQL statement selects all student record from tblStudentRecord with a Name starting with "s". 

  

SELECT * FROM tblStudentRecord WHERE Name LIKE 's%'




2. _ Wildcard in SQL :-The following SQL statement selects all student records from tblStudentRecord with a Branch starting with any character, followed by "CA": 

  

SELECT * FROM tblStudentRecord WHERE Branch LIKE '_CA'




3 (a) [charlist] Wildcard in SQL :-The following SQL statement selects all records from tblStudentRecord table with a Name starting with "s", "p", "n" or "e".

  

SELECT * FROM tblStudentRecord WHERE Name LIKE '[spne]%'




3 (b) Example :-The following SQL statement selects all records from tblStudentRecord table with a Name starting with "A" to "P" i.e. '[A-P]%' 

  

SELECT * FROM tblStudentRecord WHERE Name LIKE '[A-P]%' ORDER BY Name ASC




4. ![charlist] or ^[charlist] Wildcard in SQL :-The following SQL statement selects all records from tblStudentRecord table with a Name NOT starting with "a", "s", or "e": 

  

SELECT * FROM tblStudentRecord WHERE Name NOT LIKE '[ase]%'

                        Or
                        
SELECT * FROM tblStudentRecord WHERE Name  LIKE '[!ase]%'