LIKE Operator with Wildcards tutorial

WildCard Characters :

%    Percentage Sign : Represents zero, one or multiple characters

_     Underscore : Represents a single character



LIKE Operator with Wildcards Patterns :

Patten                            Description

LIKE "a%"                    Start with "a"                 

LIKE "%a"                    End with "a"

LIKE "%am%"              Have "am" in any position

LIKE "a%m"                 Start with "a" and Ends with "m"

LIKE "_a%"                  "a" in the second position

LIKE "__a%"                "a" in the third position

LIKE "_oy"                    "o" in the second and "y" in the third position



SELECT with LIKE Operator Syntax :

SELECT column1, column2, column3... FROM table_name WHERE column_name LIKE pattern;


SELECT with NOT LIKE Operator Syntax :

SELECT column1, column2, column3... FROM table_name WHERE column_name NOT LIKE pattern;


If we want to select records whose name is starting from "s" from table name "personal" using LIKE Operator as shown below sql query :

SELECT * FROM personal WHERE name LIKE "s%";


If we want to select records whose name contains whole word like "Mihir" from table name "personal" using LIKE Operator as shown  below sql query :

It will fetch all the records which contains "Mihir" word like "Mihir Patel", "Mihir Suthar", "Mihir Bharwad" as show below sql query :

SELECT * FROM personal WHERE name LIKE "Mihir%";


If we want to select records whose name contains "am" from table name "personal" using LIKE Operator as shown below sql query :

It will fetch all the records which contains "am" even though in any position in whole word like "Ram Kumar" contains "am" as shown below sql query :

SELECT * FROM personal WHERE name LIKE "%am%";


If we want to select records whose name starts with "r" OR "m" from table name "personal" using LIKE Operator then it will fetch all records whose name starts with "r" OR "m" like "Ram Kumar", "Mihir Patel",  "Mahesh Kaila", "Ronak Prajapati" using as shown below sql query :

SELECT * FROM personal WHERE name LIKE "r%" OR name LIKE "m%";


If we want to select records whose name not starts with "r" from table name "personal" using NOT LIKE Operator then it will fetch all records whose name not starts with "r" using as shown below sql query :

SELECT * FROM personal WHERE name NOT LIKE "r%";


If we want to select records whose name starts with small Letter "r" OR Capital Letter "R" for any specific from table name "personal" using LIKE Operator then it will fetch all records whose name starts with Small Letter "r" OR Capital Letter "R" which applied in sql query as shown below :

SELECT * FROM personal WHERE BINARY name LIKE "r%";    //Start with Small Letter Name

SELECT * FROM personal WHERE BINARY name LIKE "R%";    //Start with Capital Letter Name


If we want to select records whose name ends with "r" from table name "personal" using LIKE Operator using below shown sql query :

SELECT * FROM personal WHERE name LIKE "%r";


If we want to select records whose phone number ends with "74" from table name "personal" using LIKE Operator using below shown sql query :

SELECT * FROM personal WHERE phone LIKE "%74";


If we want to select records whose phone number NOT ends with "74" from table name "personal" using LIKE Operator using below shown sql query :

SELECT * FROM personal WHERE phone NOT LIKE "%74";


If we want to select records whose name starts with "a" and ends with "l" from table name "personal" using LIKE Operator then it fetch records like "Aarya Patel", "Aanvi Patel" using below shown sql query :

SELECT * FROM personal WHERE name LIKE "a%l";


If we want to select records whose name start with anything but its second character must be "a" and third character must be "m" from table name "personal" using LIKE Operator using below shown sql query :

SELECT * FROM personal WHERE name LIKE "_am%";


If we want to select records whose name start with anything character but its third character must be "m" from table name "personal" using LIKE Operator using below shown sql query :

SELECT * FROM personal WHERE name LIKE "__m%";

Comments

Post a Comment

Popular posts from this blog

Logical_Operators

  <?php     $age = 20 ;     $age1 = 25 ;     if ( $age >= 18 && $age <= 21 )     {         echo "You are Eligible<br>" ;     }         if ( $age >= 18 || $age <= 21 )     {         echo "You are Eligible<br>" ;     }     //Below condition is True So that "Not Operator(!)" will return False condition     if (!( $age >= 18 ))     {         echo "You are Eligible<br>" ;     }     //Below condition is False So that "Not Operator(!)" will return True condition     if (!( $age <= 18 ))     {         echo "You are Eligible<br>" ;     }     //If both condition is "True" OR "False" at that time xor will return "False" and if one condition is     //"True" and s...
Read more

SubQuery with EXISTS and NOT EXISTS in PHP

 SELECT with SubQuery Syntax : SELECT columns FROM table1 WHERE column = (SELECT columns FROM table2 WHERE condition); In "personal" table, there are 6 columns named id, name, percentage, age, gender, city. In "personal" table, column name "id" is PRIMARY KEY and column name "city" is FOREIGN KEY. In "courses" table, there are 2 columns named course_id and course_name. In "courses" table, column name "course_id" is PRIMARY KEY. In "city" table, there are 2 columns named cid and cityname. In "city" table, column name "cid" is PRIMARY KEY. If we want to select records from "personal" table who belong to Delhi from "city" table using SubQuery then we can use below shown sql query : SELECT name FROM personal WHERE city = (SELECT cid FROM city WHERE cityname = "Delhi"); If we want to select records from "personal" table who belong to Delhi and Agra from...
Read more

Get Functions

  <?php //There are many Get Functions in PHP as shown below: //get_class, get_parent_class, get_class_methods, get_class_vars, get_object_vars, get_called_class, //get_declared_classes, get_declared_interfaces, get_declared_traits, class_alias class ParentClass {     function name ()     {         echo "Class Name : " . get_class ( $this ) . "<br>" ;   //Print the particualr Class Name         //Print the particular Parent Class Name as shown below:         echo "Parent Class Name : " . get_parent_class ( $this ) . "<br>" ;     } } class ChildClass extends ParentClass {     function __construct ()     {}     function myfunction ()     {}     function myfunction2 ()     {} } $obj = new ChildClass (); $obj -> name (); echo "Class Name is : " . get_class ( $obj ) . "<br>" ; echo "Paren...
Read more