In where clause we can use almost all operators such as relational, Logical,
LIKE, Range etc. But some operators such as SET , CUBE, ROLL UP, etc are not
allowed in WHERE clause.
Wild cards
Let's create a table and insert some data in it to see wild card examples
Example
| Operator | Description | Example |
| = | Equals to | WHERE StudentName ='Kapil' |
| != or <> | Not equals to | WHERE TotalMarks != 450 |
| > | Greater than | WHERE TotalMarks > 350 |
| < | Less than | WHERE TotalMarks < 350 |
| >= | Greater than or equals to | WHERE TotalMarks >= 390 |
| <= | Less than or equals to | WHERE TotalMarks <= 390 |
| AND | Logical AND | WHERE TotalMarks > 100 AND TotalMarks < 500 |
| OR | Logical OR | WHERE TotalMarks=100 OR TotalMarks=500 |
Wild cards
| Symbol | Description |
| % | Matches with any string of zero or more characters. |
| _ | Matches with any single character. |
| [ ] | Matches with each single character listed within the brackets. |
| [ - ] | Matches with each single character within the given range. |
| [ ^ ] | Matches a single character not listed after the caret. |
Let's create a table and insert some data in it to see wild card examples
CREATE TABLE TblStudent
(
SRNo VARCHAR(15),
StudentName VARCHAR(50),
FatherName VARCHAR(50),
CourseCode VARCHAR(15),
State VARCHAR(50),
TotalMarks INT
)
GO
INSERT INTO [dbo].[TblStudent](SRNo, StudentName, FatherName, CourseCode, State, TotalMarks)
SELECT 'SR01','Harsh Kumar','Kapil Kumar','BCA','UP',350 UNION ALL
SELECT 'SR02','Sapna Kumar','Hari Kumar','MCA','UP',315 UNION ALL
SELECT 'SR03','Vipin Singh','Sunil ','HR','BBA',256 UNION ALL
SELECT 'SR04','Sumit Awasthi','Sripal','B.ED','HR',350 UNION ALL
SELECT 'SR05','Hament Kumar','Harimohan','BCA','UP',350 UNION ALL
SELECT 'SR06','Aman','Pramod','BCA','UP',470
1. Find the students whose name contain 'Kumar'.
SELECT * FROM [TblStudent] WHERE StudentName LIKE '%Kumar%'
2. Find the students whose FatherName end with 'L'.
SELECT * FROM [TblStudent] WHERE FatherName LIKE '%L'
3. Find the details of students with the first character as 'H' and the last character as 'R'.
SELECT * FROM [TblStudent] WHERE StudentName LIKE 'H%R'
4. Find out the details of students starting with 'S'
SELECT * FROM [TblStudent] WHERE StudentName LIKE 'S%'
5. Find the students with four characters in his name.
SELECT * FROM [TblStudent] WHERE StudentName LIKE '____'
6. Find the details of students that have 'A' as the second character.
SELECT * FROM [TblStudent] WHERE StudentName LIKE '_A%'
7. Find out the details of students starting with 'A','H' OR 'V'
SELECT * FROM [TblStudent] WHERE StudentName LIKE '[AHV]%'
8. Find out the details of students not starting with 'A','H' OR 'V'
SELECT * FROM [TblStudent] WHERE StudentName LIKE '[^AHV]%'
9. Find out the details of students starting with 'HA' and second character should be from 'R' or 'M'
SELECT * FROM [TblStudent] WHERE StudentName LIKE 'HA[RM]%'
10. Find out the details of students starting with any charater within the range 'A' to 'H'.
SELECT * FROM [TblStudent] WHERE StudentName LIKE '[A-H]%'
like operator in sql; operator in sql; comparison operator in sql; all operator in sql ; between operator in sql; not operator in sql; <> operator in sql; how to use like operator in sql; like operator in sql server; which is the operator to append two strings in sql; how to use like operator in sql for multiple values; which of the following is a comparison operator in sql?; equal operator in sql; not equal operator in sql; not like operator in sql; and operator in sql;wild card operator in sql
0 Comments