SQL Wildcards

SQL WILDCARD CHARACTERS:

SQL wild card character is used to substitute zero or more characters of a string.

These characters are used with the “LIKE” operator.

Wild Card Characters in SQL:

• %
• _
• []
• ^
• -

“%” :

It is used to represent the zero or more characters in a string.

Example:

Student Table

sid	sname	sage	sgender	Phonenumber
1	Abhinav	22	Male	9895678909
2	Ramya	24	Female	6687654634
3	Preetham	21	Male	9867546453
4	Nethranand	21	Male	7675643423
5	Naveen	23	Male	6567784532
6	Harshita	22	Female	9867546231
7	Bindu	26	Female	6563412768
8	Nandhini	23	Female	6785674839
9	Hashish	22	Male	9453215052
10	Rahul	21	Male	9998989898

Command For Selecting details of students from Student table whose name Starts with “N”

SQL Code:

Select * from student where sname LIKE ‘N%’;

Output:

sid	sname	sage	sgender	Phonenumber
4	Nethranand	21	Male	7675643423
5	Naveen	23	Male	6567784532
8	Nandhini	23	Female	6785674839

“_” :

       It is used to represent a single character.

Example:

      Student Table

sid	sname	sage	sgender	Phonenumber
1	Abhinav	22	Male	9895678909
2	Ramya	24	Female	6687654634
3	Preetham	21	Male	9867546453
4	Nethranand	21	Male	7675643423
5	Naveen	23	Male	6567784532
6	Harshita	22	Female	9867546231
7	Bindu	26	Female	6563412768
8	Nandhini	23	Female	6785674839
9	Hashish	22	Male	9453215052
10	Rahul	21	Male	9998989898

Command For Selecting details of students from Student table whose name ends with “a”

SQL Code:

Select * from student where sname LIKE ‘_%a’;

Output:

sid	sname	sage	sgender	Phonenumber
2	Ramya	24	Female	6687654634
6	Harshita	22	Female	9867546231

“[]”:

     It is used to represent any single character within the bracket.

Example:

      Student Table

sid	sname	sage	sgender	Phonenumber
1	Abhinav	22	Male	9895678909
2	Ramya	24	Female	6687654634
3	Preetham	21	Male	9867546453
4	Nethranand	21	Male	7675643423
5	Naveen	23	Male	6567784532
6	Harshita	22	Female	9867546231
7	Bindu	26	Female	6563412768
8	Nandhini	23	Female	6785674839
9	Hashish	22	Male	9453215052
10	Rahul	21	Male	9998989898

Command For Selecting details of students from Student table whose name ends with “a”

SQL Code:

Select * from student where sname LIKE ‘[NH]%a’;

Output:

sid	sname	sage	sgender	Phonenumber
4	Nethranand	21	Male	7675643423
5	Naveen	23	Male	6567784532
6	Harshita	22	Female	9867546231
8	Nandhini	23	Female	6785674839
9	Hashish	22	Male	9453215052

“!”:

      It represents the character that is not in the bracket.

Example:

      Student Table

sid	sname	sage	sgender	Phonenumber
1	Abhinav	22	Male	9895678909
2	Ramya	24	Female	6687654634
3	Preetham	21	Male	9867546453
4	Nethranand	21	Male	7675643423
5	Naveen	23	Male	6567784532
6	Harshita	22	Female	9867546231
7	Bindu	26	Female	6563412768
8	Nandhini	23	Female	6785674839
9	Hashish	22	Male	9453215052
10	Rahul	21	Male	9998989898

Command For Selecting details of students from the Student table whose name does not start with N and H

SQL Code:

Select * from student where sname LIKE “[!NH]%”;

Output:

sid	sname	sage	sgender	Phonenumber
1	Abhinav	22	Male	9895678909
2	Ramya	24	Female	6687654634
3	Preetham	21	Male	9867546453
7	Bindu	26	Female	6563412768
10	Rahul	21	Male	9998989898

“-“ :

     It represents the character within the given range which is present in brackets.

Example:

      Student Table

sid	sname	sage	sgender	Phonenumber
1	Abhinav	22	Male	9895678909
2	Ramya	24	Female	6687654634
3	Preetham	21	Male	9867546453
4	Nethranand	21	Male	7675643423
5	Naveen	23	Male	6567784532
6	Harshita	22	Female	9867546231
7	Bindu	26	Female	6563412768
8	Nandhini	23	Female	6785674839
9	Hashish	22	Male	9453215052
10	Rahul	21	Male	9998989898

Command For Selecting details of students from Student table whose name in which first character lies in the range [A-R].

SQL Code:

Select * from student where sname LIKE ‘[A-R]%’;

Output:

sid	sname	sage	sgender	Phonenumber
1	Abhinav	22	Male	9895678909
2	Ramya	24	Female	6687654634
3	Preetham	21	Male	9867546453
4	Nethranand	21	Male	7675643423
5	Naveen	23	Male	6567784532
6	Harshita	22	Female	9867546231
7	Bindu	26	Female	6563412768
8	Nandhini	23	Female	6785674839
9	Hashish	22	Male	9453215052
10	Rahul	21	Male	9998989898

“#”:

     It represents the numeric character in a string.

Example:

      Student Table

sid	sname	sage	sgender	Phonenumber
1	Abhinav	‘22’	Male	9895678909
2	Ramya	‘24’	Female	6687654634
3	Preetham	‘21’	Male	9867546453
4	Nethranand	‘21’	Male	7675643423
5	Naveen	‘23’	Male	6567784532
6	Harshita	‘22’	Female	9867546231
7	Bindu	‘26’	Female	6563412768
8	Nandhini	‘23’	Female	6785674839
9	Hashish	‘22’	Male	9453215052
10	Rahul	‘21’	Male	9998989898

Command For Selecting details of students from Student table whose age in which first character is ‘2’.

SQL Code:

Select * from student where sname LIKE ‘2#’;

Output:

sid	sname	sage	sgender	Phonenumber
1	Abhinav	‘22’	Male	9895678909
2	Ramya	‘24’	Female	6687654634
3	Preetham	‘21’	Male	9867546453
4	Nethranand	‘21’	Male	7675643423
5	Naveen	‘23’	Male	6567784532
6	Harshita	‘22’	Female	9867546231
7	Bindu	‘26’	Female	6563412768
8	Nandhini	‘23’	Female	6785674839
9	Hashish	‘22’	Male	9453215052
10	Rahul	‘21’	Male	9998989898