The MySQL SET data type

The MySQL SET data type is used to store a set of values chosen from a predefined list of values. Each column of the SET datatype can have zero or more values chosen from its list of values. These values are specified as a comma-separated list when inserting or updating data.

It is important to note that the list of values that can be stored in a SET column is defined at the time the table is created, and any values outside this list are not allowed.

For example, if we define a SET column like this −

test_col SET(''one'', ''two'') NOT NULL

The possible values for this column are −

• An empty string (””)
• ”one”
• ”two”
• ”one,two”

Storage of SET Data Type

A MySQL SET column can hold a maximum of 64 distinct members, which means that duplicate values are not allowed. If duplicates exist, MySQL will generate an error or a warning when strict SQL mode is enabled. Additionally, MySQL automatically removes trailing spaces from SET values when creating a table.

In MySQL, when you store a number in a SET column, the bits set in the binary representation of that number determine which set members are included in the column value. Consider the following query for a better understanding −

Create table test_table (
   ID int auto_increment primary key ,
   COL1 set(''Goa'', ''Assam'', ''Delhi'', ''Kerala'')
);

In the above query, each set member is assigned a single bit with corresponding decimal and binary values −

So, if a value of 3 is assigned to the column (binary: 0011), it selects the first two SET members, resulting in ”Goa,Assam”.

Example

First of all, let us create a table with the name test_table using the following query −

Create table test_table (
   ID int auto_increment primary key ,
   COL1 set(''Goa'', ''Assam'', ''Delhi'', ''Kerala'')
);

Following is the output obtained −

Query OK, 0 rows affected (0.02 sec)

When inserting values into a SET column, there is no specific order required for listing the elements. Even if a particular element is listed multiple times, when retrieved later, each element will appear only once, following the order specified during table creation.

Here, we are inserting the values into the set −

INSERT INTO test_table (COL1) VALUES
(''Goa,Assam''),
(''Assam,Goa''),
(''Goa,Assam,Goa''),
(''Goa,Assam,Assam''),
(''Assam,Goa,Assam'');

Output

The output produced is as shown below −

Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

Verification

Let us display all the records of the ”test_table” using the SELECT statement as follows −

SELECT * FROM test_table;

As we can see the output below, all the values in ”COL1” will appear as ”Goa,Assam” −

Example

In the following query, we are searching for the SET values in the table using the MySQL LIKE operator. It finds rows where ”COL1” contains ”GOA” anywhere, even as a substring −

SELECT * FROM test_table WHERE COL1 LIKE ''%Goa%

Output

On executing the given query, the output is displayed as follows −

Example

In here, we are fetching the rows where the values are exactly ”Goa,Assam” and in the same order as listed in the ”COL1” definition −

SELECT * FROM test_table WHERE COL1 = ''Goa,Assam

Output

The output for the above query is as given below −

Updating the SET Values

In MySQL, you can update SET elements in various ways: by replacing elements, adding elements, or removing elements from the SET data. Here are examples of each method −

Replacing SET Data

In the following query, we replace the value in the 5^th row with the number 11, which corresponds to Goa + Assam + Kerala (8 + 2 + 1) −

UPDATE test_table SET COL1 = 11 WHERE Id = 5;

Output

The query executes successfully and produces the following output −

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

To verify the changes done in the test_table, use the following SELECT query −

SELECT * FROM test_table;

Following is the output produced −

Adding Data to SET

You can add elements to an existing SET column using the CONCAT() function. In this example, we add “Kerala” to the value in the 3rd row −

UPDATE test_table SET COL1 = CONCAT(COL1, ",Kerala")
WHERE Id = 3;

Output

The output for this query is as follows −

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

To verify the changes done in the test_table, use the following SELECT query −

SELECT * FROM test_table;

The result shows the updated value −

Removing Data from SET

To remove a specific SET element, you can use the & ~ bitwise operation. In this example, we remove the “Assam” element from the value in the 4th row −

UPDATE test_table SET COL1 = COL1 & ~2 WHERE ID = 4;

Output

The output for this query is as follows −

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

Let us verify the test_table using the below query −

SELECT * FROM test_table;

Following is the table obtained −

SET Datatype Using a Client Program

We can also create column of the SET datatype using the client program.

Syntax

$sql = "CREATE TABLE test_table (ID INT auto_increment primary key, COL1 set(''Goa'', ''Assam'', ''Delhi'', ''Kerala'') )";
$mysqli->query($sql);

sql = "CREATE TABLE test_table (ID INT auto_increment primary key, COL1 set(''Goa'', ''Assam'', ''Delhi'', ''Kerala'') )";
con.query(sql);

String sql = "CREATE TABLE test_table (ID INT auto_increment primary key, COL1 set("Goa", "Assam", "Delhi", "Kerala"))";
statement.execute(sql);

sql = ''CREATE TABLE test_table (ID INT AUTO_INCREMENT PRIMARY KEY,  COL1 SET(''Goa'', ''Assam'', ''Delhi'', ''Kerala''))''
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');

$sql = ''Create table test_table (ID INT auto_increment primary key, COL1 set("Goa", "Assam", "Delhi", "Kerala") )
$result = $mysqli->query($sql);
if ($result) {
    printf("Table created successfully...!n");
}
// insert data into created table
$q ="INSERT INTO test_table (COL1) VALUES (''Goa,Assam''), (''Assam,Goa''), (''Goa,Assam,Goa''), (''Goa,Assam,Assam''), (''Assam,Goa,Assam'')";
if ($res = $mysqli->query($q)) {
    printf("Data inserted successfully...!n");
}
//now display the table records
$s = "SELECT * FROM test_table";
if ($r = $mysqli->query($s)) {
    printf("Table Records: n");
    while ($row = $r->fetch_assoc()) {
        printf(" ID: %d, Col_1: %s", $row["ID"], $row["COL1"]);
        printf("n");
    }
} else {
    printf(''Failed'');
}
$mysqli->close();

Table created successfully...!
Data inserted successfully...!
Table Records:
 ID: 1, Col_1: Goa,Assam
 ID: 2, Col_1: Goa,Assam
 ID: 3, Col_1: Goa,Assam
 ID: 4, Col_1: Goa,Assam
 ID: 5, Col_1: Goa,Assam

var mysql = require("mysql2");
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
   if (err) throw err;
   //console.log("Connected successfully...!");
   //console.log("--------------------------");
   sql = "USE TUTORIALS";
   con.query(sql);

   //create a test_table table, that accepts one column of set type.
   sql = "Create table test_table (ID INT auto_increment primary key, COL1 set(''Goa'', ''Assam'', ''Delhi'', ''Kerala'') )";
   con.query(sql);

   //insert data into created table
   sql = "INSERT INTO test_table (COL1) VALUES (''Goa,Assam''), (''Assam,Goa''), (''Goa,Assam,Goa''), (''Goa,Assam,Assam''), (''Assam,Goa,Assam'')";
   con.query(sql);

   //select datatypes of salary
   sql = `SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ''test_table'' AND COLUMN_NAME = ''col1''`;
   con.query(sql, function (err, result) {
     if (err) throw err;
     console.log(result);
   });
});

[ { DATA_TYPE: ''set'' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Set {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //Set data types...!;
         String sql = "Create table test_table (ID INT auto_increment primary key, COL1 set("Goa", "Assam", "Delhi", "Kerala"))";
         statement.execute(sql);
         System.out.println("column of a SET type created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE test_table");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Connected successfully...!
column of a SET type created successfully...!
ID int
COL1 set(''Goa'',''Assam'',''Delhi'',''Kerala'')

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# Create table with Set column
sql = ''''''
CREATE TABLE test_table (
ID INT AUTO_INCREMENT PRIMARY KEY,
COL1 SET(''Goa'', ''Assam'', ''Delhi'', ''Kerala'')
)
''''''
cursorObj.execute(sql)
print("The table is created successfully!")
# Data to be inserted
data_to_insert = [
    (''Goa,Assam'',),
    (''Assam,Goa'',),
    (''Goa,Assam,Goa'',),
    (''Goa,Assam,Assam'',),
    (''Assam,Goa,Assam'',)
]
# Insert data into the created table
insert_query = "INSERT INTO test_table (COL1) VALUES (%s)"
cursorObj.executemany(insert_query, data_to_insert)
# Commit the changes after the insert operation
connection.commit()
print("Rows inserted successfully.")
# Now display the table records
select_query = "SELECT * FROM test_table"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Test Table Data:")
for row in result:
    print(row)
cursorObj.close()
connection.close()

The table is created successfully!
Rows inserted successfully.
Test Table Data:
(1, {''Assam'', ''Goa''})
(2, {''Assam'', ''Goa''})
(3, {''Assam'', ''Goa''})
(4, {''Assam'', ''Goa''})
(5, {''Assam'', ''Goa''})

MySQL supports various types of pattern matching operations to retrieve filtered result-sets from huge database tables. In previous chapters, we have already learned about the LIKE operator for pattern matching. In this chapter, we will see another pattern matching operation based on regular expressions.

MySQL Regular Expressions

A regular expression is loosely defined as a sequence of characters that represent a pattern in an input text. It is used to locate or replace text strings using some patterns; this pattern can either be a single character, multiple characters or words, etc.

MySQL implements regular expression support using International Components for Unicode (ICU), which provides full Unicode support and is multi-byte safe.

In MySQL, it is a powerful way to perform a complex search operations in a database to retrieve desired content. And unlike the LIKE operator, the regular expressions are not restricted on search patterns (like % and _) as they use several other meta characters to expand the flexibility and control during pattern matching. This is performed using the REGEXP operator.

Syntax

Following is the basic syntax of the REGEXP operator in MySQL −

expression REGEXP pattern

Patterns used with REGEXP

Following is the table of pattern, which can be used along with the REGEXP operator.

Examples

The following example demonstrates the usage of some patterns mentioned in the table above, along with the REGEXP operator. For that, we are first creating a database table to perform the search on.

Assume we are creating a table called CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, insert some values into it using the INSERT statements given below −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Execute the following query to display all the records present in above created table −

SELECT * FROM CUSTOMERS;

Following are the records present in CUSTOMERS table −

REGEXP with Patterns −

Now, we are finding all the records in the CUSTOMERS table whose name starts with ”k” −

SELECT * FROM CUSTOMERS WHERE NAME REGEXP ''^k

Executing the query above will produce the following output −

The following query retrieves all records in CUSTOMERS table whose name ends with ”sh” −

SELECT * FROM CUSTOMERS WHERE NAME REGEXP ''sh$

Executing the query above will produce the following output −

Here, we are retrieving all the records whose name contain ”sh” −

SELECT * FROM CUSTOMERS WHERE NAME REGEXP ''sh

As we can see the output, there are only two names that contain ”sh”.

In the following query, we are finding all the names starting with a vowel and ending with ”ol” −

SELECT * FROM CUSTOMERS WHERE NAME REGEXP ''^[aeiou].*ol$

It returned an empty set because the CUSTOMERS table do not have any names who starts with vowel and ends with ”ol”

Empty set (0.00 sec)

The following query finds all the names in the CUSTOMERS table whose name starts with a consonant −

SELECT * FROM CUSTOMERS WHERE NAME REGEXP ''^[^aeiou]

Executing the query above will produce the following output −

Regular Expression Functions and Operators

Following is the table of functions and operators of regular expressions.

MySQL RLIKE Operator

The RLIKE operator in MySQL is used to search data in a database using patterns (or regular expressions), also known as pattern matching. In other words, the RLIKE operator is used to determine whether a given regular expression matches a record in a table or not. It returns 1 if the record is matched and 0, otherwise.

A regular expression is defined as a sequence of characters that represent a pattern in an input text. It is used to locate or replace text strings using some patterns; this pattern can either be a single/multiple characters or words, etc.

The functionally of this operator is equivalent to the MySQL REGEXP operator and is commonly used to search for specific patterns that meets certain criteria.

Syntax

Following is the basic syntax of the RLIKE operator in MySQL −

expression RLIKE pattern

Patterns used with RLIKE

RLIKE operator is used with several patterns or regular expressions. Following is the table of patterns that can be used along with the this operator.

Example

The following example uses the RLIKE operator to retrieve records with the help of regular expressions. To do so, we are first creating a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, insert some values into the above created table using the INSERT statements given below −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Execute the following query to display all the records present in the CUSTOMERS table −

SELECT * FROM CUSTOMERS;

Following are the records present in CUSTOMERS table −

RLIKE with Patterns −

In the following query, we are finding all the records from CUSTOMERS table whose name starts with ”ch” −

SELECT * FROM CUSTOMERS WHERE NAME RLIKE ''^ch

Executing the query above will produce the following output −

The following query displays all the records whose names ends with ”sh” −

SELECT NAME FROM CUSTOMERS WHERE NAME RLIKE ''sh$

Following are records whose name ends with ”sh” −

Here, we are retrieving the records that have names containing ”an” −

SELECT NAME FROM CUSTOMERS WHERE NAME RLIKE ''an

Following are the records −

This following query retrieves all the records whose names are ending with an vowel −

SELECT NAME FROM CUSTOMERS WHERE NAME RLIKE ''[aeiou]$

Following are the records −

The below query finds all the names starting with a consonant and ending with ”ya” −

SELECT NAME FROM CUSTOMERS WHERE NAME RLIKE ''^[^aeiou].*ya$

As we observe the output, there are no records that starts with consonant and ends with ”ya”.

Empty set (0.00 sec)

RLIKE On Strings

The RLIKE operator can perform pattern matching not only on database tables but also on individual strings. Here, the result will obtain as 1 if the pattern exists in the given string, or 0 if it doesn”t. The result is retrieved as a result-set using the SQL SELECT statement.

Syntax

Following is the basic syntax of the RLIKE operator in MySQL −

SELECT expression RLIKE pattern;

Example

In the following example, we are using the RLIKE query to check if a pattern exists in an individual string or not −

SELECT ''Welcome To Tutorialspoint!'' RLIKE ''To

The result-set will contain 1 because the pattern ”TO” exists in the specifed string.

Here, the pattern ”Hello” does not exist in the specifed string, thus it returns 0 as output.

SELECT ''Welcome To Tutorialspoint!'' RLIKE ''Hello

Executing the query above will produce the following output −

Example

REGEXP is alternative syntax to the RLIKE in MySQL. Both the operators have same result.

In the below query, if the given pattern is not found in the specifed string, this operator returns 0 −

SELECT ''Welcome to Tutorialspoint'' REGEXP ''unknown

Following is the output −

Here, the pattern ”is” does not exist in the specifed string, thus it returns 1 as output.

SELECT ''This is a sample string'' REGEXP ''is

Executing the query above will produce the following output −

Example

If either of the first two operands is NULL, the RLIKE operator returns NULL.

SELECT NULL RLIKE ''value

Following is the output −

Here, the pattern we are searching is NULL, thus the output will also be NULL.

SELECT ''Tutorialspoint'' RLIKE NULL;

Executing the query above will produce the following output −

Example

If you use the NOT clause before RLIKE operator, it returns 0 in case of a match else returns 1 (reverse of the original return values).

SELECT NOT ''This is a sample string'' RLIKE ''is

Following is the output −

Here, the pattern ”unknown” is not present in the specifed string, thus the following query returns 1 as output.

SELECT NOT ''Welcome to Tutorialspoint'' REGEXP ''unknown

Executing the query above will produce the following output −

RLIKE Operator Using a Client Program

We can also perform the MySQL RLike operator using the client programs to search data in a database using patterns (or regular expressions).

Syntax

Following are the syntaxes of this operation in various programming languages −

$sql = "SELECT * FROM person_tbl WHERE NAME RLIKE ''sh$''";
$mysqli->query($sql);

sql = "SELECT * FROM person_tbl WHERE NAME RLIKE ''sh$''";
con.query(sql);

String sql = "SELECT NAME FROM person_tbl WHERE NAME RLIKE ''^sa''";
statement.executeQuery(sql);

sql = ''SELECT NAME FROM person_tbl WHERE NAME RLIKE ''^sa''''
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$db = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');
$sql = "SELECT * FROM person_tbl WHERE NAME RLIKE ''sh$''";
if($result = $mysqli->query($sql)){
    printf("Table records: n");
    while($row = mysqli_fetch_array($result)){
        printf("Id %d, Name %s, Age %d, Address %s",
        $row[''ID''],
        $row[''NAME''],
        $row[''AGE''],
        $row[''ADDRESS'']);
        printf("n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Table records:
Id 3, Name Santosh, Age 34, Address Hyderabad
Id 6, Name Ramesh, Age 40, Address Mumbai

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});

 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
  //console.log("Connected successfully...!");
  //console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = "SELECT * FROM person_tbl WHERE NAME RLIKE ''sh$''";
 console.log("Select query executed successfully..!");
 console.log("Table records: ");
 con.query(sql);
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

Select query executed successfully..!
Table records:
[
  { ID: 3, NAME: ''Santosh'', AGE: 34, ADDRESS: ''Hyderabad'' },
  { ID: 6, NAME: ''Ramesh'', AGE: 40, ADDRESS: ''Mumbai'' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class RlikeOperator {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT NAME FROM person_tbl WHERE NAME RLIKE ''^sa''";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
                String name = rs.getString("Name");
                System.out.println("Name: " + name);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Table records:
Name: Santosh

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
#Creating a cursor object
cursorObj = connection.cursor()
rlike_operator_query = f"SELECT NAME FROM person_tbl WHERE NAME RLIKE ''^sa''"
cursorObj.execute(rlike_operator_query)
result = cursorObj.fetchall()
print("Names that start with ''sa'':")
for row in result:
    print(row[0])
cursorObj.close()
connection.close()

Names that start with ''sa'':
Santosh

MySQL NOT LIKE Operator

We have previously learned that the LIKE Operator in MySQL database is a logical operator used to perform pattern matching operation on a database table. And NOT LIKE Operator is defined as opposite of this LIKE operator.

Both LIKE and NOT LIKE operators perform pattern matching in a database table. Thus, they both need wildcards and patterns to function. However, if the LIKE operator is used to find the similar patterns mentioned using the wildcards, NOT LIKE operator is used to find all the records that do not contain the specified pattern.

• The NOT LIKE operator is nothing but the amalgamation of two SQL operators, NOT and LIKE operators. Thus, having the combination of their functionalities.

• It is used to match a particular pattern in the given string and returns 0 in case of a match and returns 1 otherwise. If either of the two operands of this function is NULL, it returns NULL as result.

• This operator is useful for finding strings that do not match a specific pattern or do not have certain characteristics.

Syntax

Following is the basic syntax of MySQL NOT LIKE operator with a SELECT statement −

SELECT column_name(s) FROM table_name
WHERE column_name NOT LIKE [condition];

Using NOT LIKE Operator with Wildcards

Wildcards are special characters used in SQL queries to match patterns in the data. Following wildcards can be used in conjunction with the NOT LIKE operator −

Note: In the NOT LIKE operator, the above wildcard characters can be used individually as well as in combinations with each other. The two mainly used wildcard characters are ”%” and ”_”.

Example

Let us begin by creating a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Using the below INSERT statements, we are inserting records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Execute the below query to display all the records present in the CUSTOMERS table −

Select * from CUSTOMERS;

Following is the CUSTOMERS table −

Now, let us use the MySQL NOTLIKE operator to displays the all the records in CUSTOMERS table whose name doesn”t starts with ”k”.

SELECT * FROM CUSTOMERS where NAME NOT LIKE ''k%

Following are the records whose name doesn”t starts with ”k” −

The following query displays the records of customers whose NAME doesn”t end with ”ik”.

SELECT * FROM CUSTOMERS where NAME NOT LIKE ''%ik

Following are the records whose name doesn”t ends with ”ik” −

Here, we are displaying all the records whose name does not contains the substring ”al”.

SELECT * FROM CUSTOMERS where NAME NOT LIKE ''%al%

Following are the records whose name doesn”t contains the substring ”al” −

The following query displays all the records whose name does not starts with ”m” and ends with ”y”.

SELECT * FROM CUSTOMERS WHERE NAME NOT LIKE ''m___y

As we can see in the output table, the seventh record is eliminated because the name starts with ”m” and ends with ”y”.

The below query displays all customer names that does not start with ”k” and have exactly 6 characters.

SELECT * FROM CUSTOMERS WHERE name NOT LIKE ''k_____

Following is the output −

Here, we are displaying the records of CUSTOMERS table, where the second character of the records in ADDRESS column is not “h”.

SELECT * FROM CUSTOMERS where ADDRESS NOT LIKE ''_h%

Following is the output −

Using NOT LIKE Operator with AND/OR Operators

We can use the MySQL NOT LIKE operator with different string patterns to choose rows, combining them with the AND or OR operators.

Syntax

Following is the syntax of using NOT LIKE operator with AND/OR operator −

SELECT column_name(s)
FROM table_name
WHERE column1 NOT LIKE pattern1 [AND|OR] column2 NOT LIKE pattern2 [AND|OR] ...;

Example

In the following example, we are displaying all records from the CUSTOMERS table where name does not start with ”k” and the address should not start with ”m” using AND operator −

SELECT * FROM CUSTOMERS
WHERE name NOT LIKE ''k%'' AND address NOT LIKE ''m%

Output

Executing the query above will produce the following output −

NOT LIKE Operator on Strings

The MySQL NOT LIKE operator can perform pattern matching not only on database tables but also on individual strings. Here, the result will obtain as 0 if the pattern exists in the given string, or 1 if it doesn”t. The result is retrieved as a result-set using the SQL SELECT statement.

Syntax

Following is the syntax of NOT LIKE operator in MySQL −

SELECT expression NOT LIKE pattern;

Example

In the following query, the pattern ”Tutorix” is not present in the specified string. So, this operator will return 1.

SELECT ''Tutorialspoint'' NOT LIKE ''Tutorix

Executing the query above will produce the following output −

Here, the pattern ”Tutorialspoint” is present in the specified string. Thus, it returns 0 as output.

SELECT ''Tutorialspoint'' NOT LIKE ''Tutorialspoint

Following is the output −

Example

If either (string or pattern operands) is NULL, this operator returns NULL. In the following query, the string is NULL, so that the output will be returned as NULL.

SELECT NULL NOT LIKE ''value

Executing the query above will produce the following output −

Here, the search pattern is NULL. So, the output will be returned as NULL.

SELECT ''Tutorialspoint'' NOT LIKE NULL;

Following is the output −

NOT LIKE Operator Using a Client Program

Besides using MySQL queries to perform the Not Like operator, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

Following are the syntaxes of this operation in various programming languages −

$sql = "SELECT * FROM EMP where Name NOT LIKE ''Su%''";
$mysqli->query($sql);

sql = "SELECT * FROM tutorials_tbl where tutorial_author Not like ''Jo%''";
con.query(sql);

String sql = "SELECT * FROM EMP where Name NOT LIKE ''Su%''";
statement.executeQuery(sql);

sql = "SELECT * FROM EMP where Name NOT LIKE ''Su%''"
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$db = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');
$sql = "SELECT * FROM EMP where Name NOT LIKE ''Su%''";
if($result = $mysqli->query($sql)){
    printf("Table records: n");
    while($row = mysqli_fetch_array($result)){
        printf("ID %d, Name %s, DOB %s, Location %s",
        $row[''ID''],
        $row[''Name''],
        $row[''DOB''],
        $row[''Location''],);
        printf("n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Table records:
ID 101, Name Amit, DOB 1970-01-08, Location Hyderabad
ID 0, Name Raja, DOB 1980-11-06, Location Goa
ID 109, Name Javed, DOB 1980-11-06, Location pune
ID 120, Name Vani, DOB 1980-11-06, Location Delhi
ID 0, Name Devi, DOB 1980-11-06, Location Goa

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});
 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
  //console.log("Connected successfully...!");
  //console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = "SELECT * FROM tutorials_tbl where tutorial_author Not like ''Jo%''";
 console.log("Select query executed successfully..!");
 console.log("Table records: ");
 con.query(sql);
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

Select query executed successfully..!
Table records:
[
  {
    tutorial_id: 2,
    tutorial_title: ''Angular Java'',
    tutorial_author: ''Abdul S'',
    submission_date: 2023-08-07T18:30:00.000Z
  },
  {
    tutorial_id: 3,
    tutorial_title: ''Learning Java'',
    tutorial_author: ''Sanjay'',
    submission_date: 2007-05-05T18:30:00.000Z
  },
  {
    tutorial_id: 4,
    tutorial_title: ''Python Tutorial'',
    tutorial_author: ''Sasha Lee'',
    submission_date: 2016-09-03T18:30:00.000Z
  },
  {
    tutorial_id: 5,
    tutorial_title: ''Hadoop Tutorial'',
    tutorial_author: ''Chris Welsh'',
    submission_date: 2023-08-07T18:30:00.000Z
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class NotLikeOperator {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String user = "root";
      String password = "password";
      ResultSet rs;
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT * FROM EMP where Name NOT LIKE ''Su%''";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
               String id = rs.getString("id");
               String name = rs.getString("Name");
               String dob = rs.getString("dob");
               String location = rs.getString("location");
               System.out.println("Id: " + id + ", Name: " + name + ", Dob: " + dob + ", Location: " + location);
            }
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

Table records:
Id: 101, Name: Amit, Dob: 1970-01-08, Location: Hyderabad
Id: MyID2, Name: Raja, Dob: 1980-11-06, Location: Goa
Id: MyID2, Name: Raja, Dob: 1980-11-06, Location: Goa
Id: 109, Name: Javed, Dob: 1980-11-06, Location: pune
Id: 120, Name: Vani, Dob: 1980-11-06, Location: Delhi
Id: oo1, Name: Devi, Dob: 1980-11-06, Location: Goa

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
#Creating a cursor object
cursorObj = connection.cursor()
notlike_operator_query = f"SELECT * FROM EMP where Name NOT LIKE ''Su%''"
cursorObj.execute(notlike_operator_query)
# Fetching all the results
results = cursorObj.fetchall()
# display the results
print("Employees whose name does not start with ''Su'':")
for row in results:
    print(row)
cursorObj.close()
connection.close()

Employees whose name does not start with ''Su'':
(''101'', ''Amit'', datetime.date(1970, 1, 8), ''Hyderabad'')
(''MyID2'', ''Raja'', datetime.date(1980, 11, 6), ''Goa'')
(''109'', ''Javed'', datetime.date(1980, 11, 6), ''pune'')
(''120'', ''Vani'', datetime.date(1980, 11, 6), ''Delhi'')
(''oo1'', ''Devi'', datetime.date(1980, 11, 6), ''Goa'')

MySQL NOT REGEXP Operator

Technically, a regular expression is defined as a sequence of characters that represent a pattern in an input text. It is used to locate or replace text strings using some patterns; this pattern can either be a single character, multiple characters or words, etc.

In MySQL, the REGEXP operator is a powerful tool to perform complex search operations in a database to retrieve required data using regular expressions. And unlike the LIKE operator, the REGEXP operator is not restricted on search patterns (like % and _), as it uses several other meta characters to expand the flexibility and control during pattern matching.

NOT REGEXP Operator is a negation to this REGEXP operator. It is used to retrieve all the records present in a database that do not satisfy the specified pattern. Let us learn about this operator in detail further in this chapter.

Syntax

Following is the basic syntax of the MySQL NOT REGEXP operator −

expression NOT REGEXP pattern

Note − Since this is just a negation, the functionality of a NOT REGEXP operator will be the same as a REGEXP operator.

Examples

Let us start by creating a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The below INSERT statement inserts 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Execute the following query to display all the records present in CUSTOMERS table −

Select * from CUSTOMERS;

Following is the CUSTOMERS table −

Now, let us show the usage of NOT REGEXPM operator using several queries on this table.

Query to find all the names not starting with ”ra” −

SELECT * FROM CUSTOMERS WHERE NAME NOT REGEXP ''^ra

Following is the output −

Query to retrieve all the records whose names not ending with ”ik” −

SELECT * FROM CUSTOMERS WHERE NAME NOT REGEXP ''ik$

Following is the output −

Following is the query to find all the names that do not contain ”an” −

SELECT * FROM CUSTOMERS WHERE NAME NOT REGEXP ''an

Following is the output −

Example

The NOT RLIKE is alternative syntax to the NOT REGEXP in MySQL. Both the operators have same result. If either of the first two operands is NULL, this operator returns NULL.

In the below query, the string value is NULL. Thus it gives output as NULL.

SELECT NULL NOT RLIKE ''value

Following is the output −

Here, the specified pattern is NULL. So, the output will be retrieved as NULL.

SELECT ''Tutorialspoint'' NOT REGEXP NULL;

Following is the output −

NOT REGEXP Operator Using a Client Program

Besides using MySQL queries to perform the NOT REGEXP operator, we can also use client programs such as PHP, Node.js, Java, and Python to achieve the same result.

Syntax

Following are the syntaxes of this operation in various programming languages −

$sql = "SELECT * FROM person_tbl WHERE NAME NOT REGEXP ''^sa''";
$mysqli->query($sql);

sql = "SELECT * FROM person_tbl WHERE NAME NOT REGEXP ''^sa''";
con.query(sql);

String sql = "SELECT * FROM person_tbl WHERE NAME NOT REGEXP ''^sa''";
statement.executeQuery(sql);

sql = "SELECT * FROM person_tbl WHERE NAME NOT REGEXP ''^sa''"
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$db = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');
$sql = "SELECT * FROM person_tbl WHERE NAME NOT REGEXP ''^sa''";
if($result = $mysqli->query($sql)){
    printf("Table records(where the name does not start with ''sa''): n");
    while($row = mysqli_fetch_array($result)){
        printf("ID %d, Name %s, Age %d, Address %s",
        $row[''ID''],
        $row[''NAME''],
        $row[''AGE''],
        $row[''ADDRESS''],);
        printf("n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Table records(where the name does not start with ''sa''):
ID 1, Name John, Age 33, Address New York
ID 2, Name Ram, Age 29, Address Pune
ID 4, Name Tanya, Age 26, Address Paris
ID 5, Name Anmol, Age 28, Address Surat
ID 6, Name Ramesh, Age 40, Address Mumbai

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});
 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
  //console.log("Connected successfully...!");
  //console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = "SELECT * FROM person_tbl WHERE NAME NOT REGEXP ''^sa''";
 console.log("Select query executed successfully..!");
 console.log("Table records: ");
 con.query(sql);
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

Select query executed successfully..!
Table records:
[
  { ID: 1, NAME: ''John'', AGE: 33, ADDRESS: ''New York'' },
  { ID: 2, NAME: ''Ram'', AGE: 29, ADDRESS: ''Pune'' },
  { ID: 4, NAME: ''Tanya'', AGE: 26, ADDRESS: ''Paris'' },
  { ID: 5, NAME: ''Anmol'', AGE: 28, ADDRESS: ''Surat'' },
  { ID: 6, NAME: ''Ramesh'', AGE: 40, ADDRESS: ''Mumbai'' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class regexp_not {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT * FROM person_tbl WHERE NAME NOT REGEXP ''^sa''";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("Name");
                String age = rs.getString("AGE");
                String address = rs.getString("ADDRESS");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Table records:
Id: 1, Name: John, Age: 33, Address: New York
Id: 2, Name: Ram, Age: 29, Address: Pune
Id: 4, Name: Tanya, Age: 26, Address: Paris
Id: 5, Name: Anmol, Age: 28, Address: Surat
Id: 6, Name: Ramesh, Age: 40, Address: Mumbai

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
   host=''localhost'',
   user=''root'',
   password=''password'',
   database=''tut''
)
#Creating a cursor object
cursorObj = connection.cursor()
notregexp_operator_query = f"SELECT * FROM person_tbl WHERE NAME NOT REGEXP ''^sa''"
cursorObj.execute(notregexp_operator_query)
# Fetching all the results
results = cursorObj.fetchall()
# display the results
print("Persons whose name does not start with ''sa'' using NOT REGEXP:")
for row in results:
    print(row)
cursorObj.close()
connection.close()

Persons whose name does not start with ''sa'' using NOT REGEXP:
(1, ''John'', 33, ''New York'')
(2, ''Ram'', 29, ''Pune'')
(4, ''Tanya'', 26, ''Paris'')
(5, ''Anmol'', 28, ''Surat'')
(6, ''Ramesh'', 40, ''Mumbai'')

MySQL supports various types of pattern matching operations to retrieve filtered result-sets from huge database tables. But, pattern matching with regular expressions is a powerful way to perform a complex search.

As we have seen in the previous chapter, the MySQL regexp_instr() function is used to return the position of the pattern found. But if you want to just detect whether the pattern is present in the data or not, you can use the regexp_like() function.

MySQL REGEXP_LIKE() Function

The MySQL regexp_like() function is also used to search for a string that is matched with specified patterns. This function returns 1 if this string matches the specified pattern, 0 if there is no match, or NULL if the string or the pattern is NULL. The pattern used in this function can be an extended regular expression and not just an ordinary string.

Syntax

Following is the syntax of the MySQL regexp_like() function −

REGEXP_LIKE(expr, pattern[, match_type])

Parameters

The regexp_like() function takes following parameter values −

• expr: The string in which search is performed

• pattern: The pattern that is searched in the string

• match_type: (Optional argument) A string that specifies how to perform matching; includes case-sensitive matching(c), case-insensitive matching(i), multiple-line mode(m), matching line terminators(n), matching Unix-only line endings(u).

Example

In this example, we are performing a search operation on a simple string using the MySQL REGEXP_LIKE() function −

SELECT REGEXP_LIKE(''Welcome To Tutorialspoint!'', ''To'')
AS RESULT;

The search pattern ”To” is present in the string, so it returned 1 as output.

Now, if there is no match found in the string, the result will be obtained as ”0” as shown below −

SELECT REGEXP_LIKE(''Welcome To Tutorialspoint!'', ''Hello'')
AS RESULT;

Following is the output −

Let us also pass the optional arguments to this function as case-sensitive matching(c) and observe the result −

SELECT REGEXP_LIKE(''Welcome To Tutorialspoint!'', ''t'', ''c'')
AS RESULT;

Executing the query above will produce the following output −

Example

If either of the first two arguments passed to this function is NULL, this function returns NULL. In the below query, we are passing NULL to the string parameter.

SELECT REGEXP_LIKE(NULL, ''value'') AS Result;

Following is the output −

Here, we are passing NULL as the search pattern −

SELECT REGEXP_LIKE(''Welcome to Tutorialspoint'', NULL)
AS Result;

Executing the query above will produce the following output −

Example

In another example, let us perform a search operation on a database table named CUSTOMERS using the REGEXP_LIKE() function. Firstly, let us create the table using the following query −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The following query inserts 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Execute the following query to display all the records of CUSTOMERS table −

Select * from CUSTOMERS;

Following is the CUSTOMERS table −

The following query selects records from the CUSTOMERS table where the NAME column starts with the letter ”K” −

SELECT REGEXP_LIKE(NAME, ''^K'')
AS RESULT FROM CUSTOMERS;

If there is a name that starts with letter ”K” it gives 1 as output, else 0 −

The following query checks whether the ”ADDRESS” column in the ”CUSTOMERS” table contains the letter ”K” (case-insensitive). If the address contains ”K” or ”k,” the result is 1; otherwise, it”s 0.

SELECT REGEXP_LIKE(ADDRESS, ''R'', ''i'')
AS RESULT FROM CUSTOMERS;

As we can see in the output table, 6th and 7th row in ADDRESS column contains a letter ”K” (case-insensitive) −

REGEXP_LIKE() Function Using a Client Program

Besides using MySQL queries to perform the REGEXP_Like() function, we can also use client programs such as PHP, Node.js, Java, and Python to achieve the same result.

Syntax

Following are the syntaxes of this operation in various programming languages −

$sql = "SELECT REGEXP_LIKE(''Welcome To Tutorialspoint!'', ''To'') AS RESULT";
$mysqli->query($sql);

sql = "SELECT REGEXP_LIKE(''Welcome To Tutorialspoint!'', ''To'') AS RESULT";
con.query(sql);

String sql = "SELECT REGEXP_LIKE(''Welcome To Tutorialspoint!'', ''To'') AS RESULT";
statement.executeQuery(sql);

sql = "SELECT REGEXP_LIKE(''Welcome To Tutorialspoint!'', ''To'') AS RESULT"
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$db = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');
$sql = "SELECT REGEXP_LIKE(''Welcome To Tutorialspoint!'', ''To'') AS RESULT";
if($result = $mysqli->query($sql)){
    while($row = mysqli_fetch_array($result)){
        printf("Result: %d", $row[''RESULT'']);
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Result: 1

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});
 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
  //console.log("Connected successfully...!");
  //console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = "SELECT REGEXP_LIKE(''Welcome To Tutorialspoint!'', ''To'') AS RESULT";
 console.log("Select query executed successfully..!");
 console.log("Table records: ");
 con.query(sql);
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

Select query executed successfully..!
Table records:
[ { RESULT: 1 } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class regexp_like {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT REGEXP_LIKE(''Welcome To Tutorialspoint!'', ''To'') AS RESULT";
            rs = st.executeQuery(sql);
            while(rs.next()) {
                String result = rs.getString("RESULT");
                System.out.println("Result: " + result);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Result: 1

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
regexp_like_query = f"SELECT REGEXP_LIKE(''Welcome To Tutorialspoint!'', ''To'') AS RESULT"
cursorObj.execute(regexp_like_query)
# Fetching all the results
results = cursorObj.fetchall()
# Display the result
print("Result of REGEXP_LIKE() Function:")
for row in results:
    result = row[0]
    if result:
        print("The pattern ''To'' is found in the given string.")
    else:
        print("The pattern ''To'' is not found in the given string.")
cursorObj.close()
connection.close()

Result of REGEXP_LIKE() Function:
The pattern ''To'' is found in the given string.

MySQL supports various types of pattern matching operations to retrieve filtered result-sets from huge database tables. But, pattern matching with regular expressions is a powerful way to perform a complex search.

A regular expression is technically defined as a sequence of characters that represent a pattern in an input text. It is used to locate or replace text strings using some patterns; this pattern can either be a single character, multiple characters or words, etc.

These regular expressions in MySQL provide various functions and operators to easily perform the search operations. One such function is regexp_instr() function.

MySQL REGEXP_INSTR() Function

The MySQL regexp_instr() function is used to match specified patterns with either a string or the data in database tables. This function returns the starting index of the substring of a string that matches the specified pattern, returns 0 if there is no match, or NULL if the string or the pattern is NULL. Character indices of this string starts at 1.

Syntax

Following is the syntax of the MySQL regexp_instr() function −

REGEXP_INSTR(expr, pattern[, pos[, occurrence[, return_option[, match_type]]]])

Where expr is the string in which the search is to be performed and pat is the pattern/regular expression that is to be searched. In addition to the expression and string values this method accepts the following optional parameters.

Parameters

The regexp_instr() function takes following parameter values −

• expr: The string in which search is performed

• pattern: The pattern that is searched in the string

Following are the optional arguments that can be passed to this function −

• pos: The position in expr at which to start the search. If omitted, the default is 1.

• occurrence: Which occurrence of a match to search for. If omitted, the default is 1.

• return_option: Which type of position to return. If this value is 0, REGEXP_INSTR() returns the position of the matched substring”s first character. If this value is 1, REGEXP_INSTR() returns the position following the matched substring. If omitted, the default is 0.

• match_type:This is a string which consists of various characters representing the desired features of the match this may contain one or all of these characters. Following are various characters using which you can specify the match type.

• c This character indicates the case-sensitive matching.

• i This character indicates the case-insensitive matching.

• m This character indicates the i.e., multiple lines (separated by line terminators) with in a single string are recognized.

• n If this character is specified, the dot (.) character matches line terminators.

• u If this character is specified, only new line character is recognized as line ending by ., ^, and $.

Example

In this example, we are performing a search operation on a simple string using the MySQL REGEXP_INSTR() function −

SELECT REGEXP_INSTR(''Welcome To Tutorialspoint!'', ''To'') AS RESULT;

The pattern ”To” is found at 9th index −

If there is no match found in the string, the return value will be ”0” −

SELECT REGEXP_INSTR(''Welcome To Tutorialspoint!'', ''xx'') AS RESULT;

Following is the output −

Example

Let us also pass optional arguments to this function and observe the result. Here, the search search position starts at 5 to find the 2nd occurrence of ”T” after that position. As the return option is set to 1, the position following the match is returned. −

SELECT REGEXP_INSTR(''Welcome To Tutorialspoint!'', ''T'', 5, 2, 1) AS RESULT;

Output

Following is the output −

Example

The following query searches for the position for any alphabetic character in the provided string ”9848032919”. If found, it returns 1. Else, 0.

SELECT REGEXP_INSTR(''9848032919'', ''[[:alpha:]]'');

Output

Executing the query above will produce the following output −

Example

The below query searches for the position of either ”town” or ”city” in the provided string −

SELECT REGEXP_INSTR(''Vishakapatnam is city of destiny '', ''town|city'')
As Result;

Output

The output for the above query is produced as given below −

Example

If either of the first two arguments passed to this function is NULL, this function returns NULL. Here, we are passing ”NULL” as search pattern.

SELECT REGEXP_INSTR(''Tutorialspoint'', NULL)
As Result;

If we compile and run the query, the result is produced as follows −

In the following query, we are passing ”NULL” to the string parameter.

SELECT REGEXP_INSTR(NULL, ''to'')
As Result;

When we execute the query above, the output is obtained as follows −

Example

In another example, let us perform a search operation on a database table named CUSTOMERS using the REGEXP_INSTR() function. Firstly, let us create the table using the following query −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Insert some records into the above created table using the following INSERT query −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Execute the below query to display all the records present in CUSTOMERS table −

Select * from CUSTOMERS;

Following is the CUSTOMERS table −

The following query selects the position of the first occurrence of a string that starts with the letter ”K” from the NAME column in the CUSTOMERS table −

SELECT REGEXP_INSTR(NAME, ''^K'')
AS RESULT FROM CUSTOMERS;

As we can see in the output below, there are three string in NAME column that starts with letter K.

Client Program

We can also perform the MySQL REGEXP_INSTR() function using the client programs (such as PHP, Node.js, Java, Python) to match specified pattern with either a string or the data in database tables.

Syntax

Following are the syntaxes of this operation in various programming languages −

$sql = "SELECT REGEXP_INSTR(''Welcome To Tutorialspoint!'', ''To'') AS RESULT";
$mysqli->query($sql);

sql = "SELECT REGEXP_INSTR(''Welcome To Tutorialspoint!'', ''To'') AS RESULT";
con.query(sql);

String sql = "SELECT REGEXP_INSTR(''Welcome To Tutorialspoint!'', ''To'') AS RESULT";
statement.executeQuery(sql);

sql = "SELECT REGEXP_INSTR(''Welcome To Tutorialspoint!'', ''To'') AS RESULT"
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$db = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');
$sql = "SELECT REGEXP_INSTR(''Welcome To Tutorialspoint!'', ''To'') AS RESULT";
if($result = $mysqli->query($sql)){
    while($row = mysqli_fetch_array($result)){
        printf("Result: %d", $row[''RESULT'']);
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Result: 9

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});
 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
  //console.log("Connected successfully...!");
  //console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = "SELECT REGEXP_INSTR(''Welcome To Tutorialspoint!'', ''To'') AS RESULT";
 console.log("Select query executed successfully..!");
 console.log("Table records: ");
 con.query(sql);
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

Select query executed successfully..!
Table records:
[ { RESULT: 9 } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class regexp_instr {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT REGEXP_INSTR(''Welcome To Tutorialspoint!'', ''To'') AS RESULT";
            rs = st.executeQuery(sql);
                  while(rs.next()) {
                String result = rs.getString("RESULT");
                System.out.println("Result: " + result);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Result: 9

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
regexp_instr_query = f"SELECT REGEXP_INSTR(''Welcome To Tutorialspoint!'', ''To'') AS RESULT"
cursorObj.execute(regexp_instr_query)
# Fetching all the results
results = cursorObj.fetchall()
# Display the result
print("Result of REGEXP_INSTR() Function:")
for row in results:
    position = row[0]
    if position > 0:
        print(f"The pattern ''To'' found at position {position}")
    else:
        print("The pattern ''To'' not found in the given string")
cursorObj.close()
connection.close()

Result of REGEXP_INSTR() Function:
The pattern ''To'' found at position 9

Regular expressions in MySQL are used in search operations to not only filter records but also replace the pattern occurrences in a string.

Consider a scenario where you noticed a spelling error among the huge sets of data present in a MySQL database. Now, you are supposed to correct all occurrences of these errors in this database without disturbing the other data. This is where regular expressions are extremely advantageous.

You can use regular expressions to find the accurate occurrences of the same error and replace it with the right characters. This is done using the regexp_replace() function.

MySQL REGEXP_REPLACE() Function

The MySQL regexp_replace() function is used to find and replace occurrences of a string that match specific patterns. If there”s a match, it replaces the string with another. If there”s no match, it returns the original string. If the string or pattern is NULL, it returns NULL. You can use a regular expression or a simple string as the pattern in this function.

Syntax

Following is the syntax of the MySQL regexp_replace() function −

REGEXP_REPLACE(expr, pattern, repl[, pos[, occurrence[, match_type]]])

Parameters

The regexp_replace() function takes following parameter values −

• expr: The string in which search is performed

• pattern: The pattern that is searched in the string

• repl: The replacement string

This method also accepts following optional arguments −

• pos − Starting position of the search

• occurrence − Which occurrence of a match to replace. If omitted, the default is 0 so it replaces all occurrences.

• match_type − A string that specifies how to perform matching.

Example

In the following query, we are performing a search operation on a simple string using the MySQL REGEXP_REPLACE() function −

SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''Welcome'', ''Welll'')
AS RESULT;

As we can observe the output below, the string ”Welcome” is found and replaced with ”Welll” −

But if the pattern is not found in the string, the original string is displayed by the function. Look at the following query −

SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''H'', ''Hi'') AS RESULT;

On executing the given query, the output is displayed as follows −

Example

Let us also try to pass optional arguments to this function as case-insensitive matching(i). Here, the search starts from the 10th position in the given string; and as we are passing the occurrence value as 1, only the first occurrence of the letter ”t” after 10th position will be replaced irrespective of its case −

SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''t'', ''x'', 10, 1, ''i'') AS RESULT;

Output

The output for the program above is produced as given below −

Example

The following query replaces all the occurrences of the string “is” in the given text −

SELECT REGEXP_REPLACE(''This is a sample string'', ''is'', ''@@@@'')
As Result;

Output

On executing the given query, the output is displayed as follows −

Example

The following query replaces only the first occurrence of the string “This” in the given text with “That” −

SELECT REGEXP_REPLACE(''This is a test and This is another test'', ''^This'', ''That'')
As Result;

Output

The output for the query above is produced as given below −

Example

Here, the below query replace the words ”wall” or ”floor” with the word ”bed” in the given string using the MySQL REGEXP_REPLACE() function −

SELECT REGEXP_REPLACE (''Humpty dumpty sat on a wall and slept on the floor'', ''wall|floor'', ''bed'') As Result;

Output

On executing the given program, the output is displayed as follows −

Example

The following query replaces the first occurrence of the string “eat” with the string “drink” in the provided input string.

In the query, the fourth parameter “1” specifies the position to start the search and the fifth parameter “1” is the number of replacements to be made. Therefore, only the first occurrence of “eat” is replaced with “drink”.

SELECT REGEXP_REPLACE(''eat sleep repeat and eat'', ''eat'', ''drink'', 1, 1)
As Result;

Output

Following is the output −

Example

If either of the first two arguments passed to this function is NULL, this function returns NULL. Here, we are passing NULL to the string parameter.

SELECT REGEXP_REPLACE(NULL, ''value'', ''test'') As Result;

Following is the output −

If we pass NULL to the pattern parameter, it returns NULL as output.

SELECT REGEXP_REPLACE(''Welcome to Tutorialspoint'', NULL, ''sample'')
As Result;

The output for the query above is produced as given below −

If you pass empty string as the replacement string, this function returns NULL.

SELECT REGEXP_REPLACE(''Welcome to Tutorialspoint'', NULL, '''')
As Result;

On executing the given query, the output is displayed as follows −

Example

In another example, let us try to perform a search operation on a database table named CUSTOMERS using the REGEXP_REPLACE() function. First of all, let us create the table using the following query −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The following query inserts 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Execute the following SELECT statement to display all the records of CUSTOMERS table −

Select * from CUSTOMERS;

Following is the CUSTOMERS table −

The following query uses the REGEXP_REPLACE() function to update the NAME column in the person_tbl table. It looks for names that start with the letter ”A” and replaces that ”A” with ”An”.

SELECT REGEXP_REPLACE(NAME, ''^A'', ''An'') AS Result FROM CUSTOMERS;

On executing the given query, the output is displayed as follows −

But if the pattern is not found in any record of the table, the original values of the table are displayed by the function. Look at the following query −

SELECT REGEXP_REPLACE(ADDRESS, ''^Z'', ''P'') AS RESULT FROM CUSTOMERS;

There is no record in ADDRESS column that starts with letter ”Z”. So, it returned the original records as output −

The following query is using the REGEXP_REPLACE function to replace the second occurrence of the letter ”r” with ”R” in the ADDRESS column of the CUSTOMERS table −

SELECT REGEXP_REPLACE(ADDRESS, ''r'', ''R'', 2, 0, ''c'')
AS RESULT FROM CUSTOMERS;

As we can see in the output, the records ”Hyderabad” and ”Indore” has letter ”r” in it. And they are replaced by ”R” −

REGEXP_REPLACE() Funcion Using a Client Program

We can also perform the MySQL REGEXP_REPLACE function using the client programs to find and replace occurrences of a string that match specific patterns.

Syntax

Following are the syntaxes of this operation in various programming languages −

$sql = "SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''Welcome'', ''Welcom'')";
$mysqli->query($sql);

sql = "SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''Welcome'', ''Welcom'')";
con.query(sql);

String sql = "SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''Welcome'', ''Welcom'')";
statement.executeQuery(sql);

sql = "SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''Welcome'', ''Welcom'')"
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$db = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');
$sql = "SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''Welcome'', ''Welcom'') AS RESULT";
if($result = $mysqli->query($sql)){
    while($row = mysqli_fetch_array($result)){
        printf("Result: %s", $row[''RESULT'']);
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Result: Welcom To Tutorialspoint!

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});
 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
  //console.log("Connected successfully...!");
  //console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = "SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''Welcome'', ''Welcom'') AS RESULT";
 console.log("Select query executed successfully..!");
 console.log("Table records: ");
 con.query(sql);
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

Select query executed successfully..!
Table records:
[ { RESULT: ''Welcome To Tutorialspoint!'' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class regexp_replace {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''Welcome'', ''Welcom'') AS RESULT";
            rs = st.executeQuery(sql);
            while(rs.next()) {
                String result = rs.getString("RESULT");
                System.out.println("Result: " + result);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Result: Welcom To Tutorialspoint!

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
regexp_replace_query = f"SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''Welcome'', ''Welcom'') AS RESULT;"
cursorObj.execute(regexp_replace_query)
# Fetching all the results
results = cursorObj.fetchall()
# Display the result
print("Result of REGEXP_REPLACE() Function:")
for row in results:
    result = row[0]
    print(f"The modified string is: ''{result}''")
cursorObj.close()
connection.close()

Result of REGEXP_REPLACE() Function:
The modified string is: ''Welcom To Tutorialspoint!''

The MySQL Full-Text Search allows us to search for a text-based data, stored in the database. Before performing the full-text search in a column(s) of table, we must create a full-text index on those columns.

The FTS (full-text search) provides the capability to match the searched string value through large text content such as blogs, articles, etc.

MySQL Full-Text Search

To perform a Full-Text Search on a MySQL table, we use MATCH() and AGAINST() functions in a WHERE clause of an SQL SELECT statement.

Stop words are words that are commonly used (such as ”on”, ”the”, or, ”it”) in sentences and will be ignored during the searching process.

The basic syntax to perform a full-text search on a MySQL is as follows −

SELECT column_name(s) FROM table_name
WHERE MATCH(col1, col2, ...)
AGAINST(expression [search_modifier])

Here,

• MATCH() function contains one or more columns separated by commas to be searched.
• AGAINST() function contains a search string to use for the full-text search.

Key Points of MySQL Full-Text Search

Following are some key points about the full-text search in MySQL −

• Either InnoDB or MyISAM tables use the full-text indexes. The minimum length of the word for full-text searches is three characters for InnoDB tables and four characters for MyISAM tables.
• Full-Text indexes can be created on text-based columns (CHAR, VARCHAR or TEXT columns).
• A FULLTEXT index can be defined while creating the table using CREATE TABLE statement or can be defined later using the ALTER TABLE or CREATE INDEX statements.
• Without FULLTEXT index, it is faster to load large data sets into a table than to load data into a table which has an existing FULLTEXT index. Therefore it is recommended to create the index after loading data.

Types of Full-Text Searches

There are three types of full-text searches. The same is described below:

• Natural Language Full-Text Searches: This allows the user to enter the search query in a natural human language without any special characters or operators. The search engine will examine the query entered by the user and returns the relevant results based on the user”s intent.
• Boolean Full-Text Searches: This allows us to perform a full-text search based on very complex queries in the Boolean mode along with Boolean operators such as +, -, >,
• Query Expansion Searches: This expands the user”s query to widen the search result of the full-text searches based on automatic relevance feedback or blind query expansion.

Creating MySQL FULLTEXT Index

In MySQL, we can define a full-text index on particular column while creating a new table or on an existing table. This can be done in three ways:

• Using the FULLTEXT Keyword

• Using the ALTER TABLE Statement

• Using the CREATE INDEX Statement

Using the FULLTEXT Keyword

To define full-text index on a column while creating a new table, we use the FULLTEXT keyword on that column within the CREATE TABLE query. Following is the syntax −

CREATE TABLE table_name(
   column1 data_type,
   column2 data_type,
   ...,
   FULLTEXT (column1, column2, ...)
);

Let us create first a table named FILMS and define the full-text index on NAME and DIRECTOR columns, using the following query −

CREATE TABLE FILMS (
   ID int auto_increment not null primary key,
   NAME varchar(50),
   DIRECTOR TEXT,
   FULLTEXT (NAME, DIRECTOR)
);

Now, let us insert values into this table using the following query −

INSERT INTO FILMS (NAME, DIRECTOR) VALUES
(''RRR'', ''Directed by Rajamouli''),
(''Bahubali'', ''Directed by Rajamouli''),
(''Avatar'', ''Directed by James cameron''),
(''Robot'', ''Directed by Shankar'');

The table will be created as −

Here, we are fetching all the rows from the FILMS table where the NAME or DIRECTOR column matches the string ‘Rajamouli’ using the MATCH and AGAINST functions as shown below −

SELECT * FROM FILMS
WHERE MATCH (NAME, DIRECTOR)
AGAINST (''Rajamouli'');

As we can see in the output below, the full-text search has been performed against a string ‘Rajamouli’ and it returned the rows which contains this string.

Using the ALTER TABLE Statement

In MySQL, we can create full-text index on particular columns of an existing table using the ALTER TABLE statement. Following is the syntax −

ALTER TABLE table_name
ADD FULLTEXT (column1, column2,...)

In this example, we are defining a full-text index named FULLTEXT on NAME and DIRECTOR columns of the previously created FILMS table −

ALTER TABLE FILMS ADD FULLTEXT (NAME, DIRECTOR);

Now, let us retrieve all the rows from the FILMS table where the NAME or DIRECTOR column matches the string ”Shankar”.

SELECT * FROM FILMS
WHERE MATCH (NAME, DIRECTOR)
AGAINST (''Shankar'');

Following is the output −

Using the CREATE INDEX Statement

In MySQL, we can also create a full-text index for an existing table using the CREATE INDEX statement. Following is the syntax −

CREATE FULLTEXT INDEX index_name
ON table_name (index_column1, index_column2,...)

We are creating a full-text index with the name INDEX_FULLTEXT on the NAME and DIRECTOR column of the FILMS table −

CREATE FULLTEXT INDEX INDEX_FULLTEXT ON FILMS (NAME, DIRECTOR);

Now, let us retrieve all the rows from the FILMS table where the NAME or DIRECTOR column matches the string value as shown in the below query −

SELECT * FROM FILMS
WHERE MATCH(NAME, DIRECTOR)
AGAINST (''James Cameron'');

Following is the output −

Dropping MySQL FULLTEXT index

In MySQL, we can remove or drop a full-text index from a table using the ALTER TABLE DROP INDEX statement.

Following is the syntax −

ALTER TABLE table_name DROP INDEX index_name;

In the following query, we will delete the previously created full-text index −

ALTER TABLE FILMS DROP INDEX INDEX_FULLTEXT;

Let us verify whether the index is dropped or not by executing the below query −

SELECT * FROM FILMS
WHERE MATCH(NAME, DIRECTOR)
AGAINST (''James Cameron'');

As we can see in the output, the full-text index is removed on the NAME and DIRECTOR columns.

ERROR 1191 (HY000): Can''t find FULLTEXT index matching the column list

Full-Text Search Using Client Program

In addition to performing the full-text search using MySQL Query, we can also do so using the client program.

Syntax

$sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
$mysqli->query($sql);

sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
con.query(sql);

String sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
statement.execute(sql);

fulltext_search_query = "SELECT * FROM FILMS WHERE MATCH (NAME, DIRECTOR) AGAINST (''Rajamouli'')"
cursorObj.execute(fulltext_search_query)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf(''Connected successfully.
'');
//creating a table films that stores fulltext.
$sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
$result = $mysqli->query($sql);
if ($result) {
    printf("Table created successfully...!n");
}
//insert data
$q = "INSERT INTO FILMS (NAME, DIRECTOR) VALUES (''RRR'', ''The film RRR is directed by Rajamouli''), (''Bahubali'', ''The film Bahubali is directed by Rajamouli''),
      (''Avatar'', ''The film Avatar is directed by James cameron''), (''Robot'', ''The film Robot is directed by Shankar'')";
if ($res = $mysqli->query($q)) {
    printf("Data inserted successfully...!n");
}
//now display the table records
$s = "SELECT * FROM FILMS WHERE MATCH (NAME, DIRECTOR) AGAINST (''Rajamouli'')";
if ($r = $mysqli->query($s)) {
    printf("Table Records: n");
    while ($row = $r->fetch_assoc()) {
        printf(" ID: %d, Name: %s, Director: %s", $row["ID"], $row["NAME"], $row["DIRECTOR"]);
        printf("n");
    }
} else {
    printf(''Failed'');
}
$mysqli->close();

Table created successfully...!
Data inserted successfully...!
Table Records:
 ID: 1, Name: RRR, Director: The film RRR is directed by Rajamouli
 ID: 2, Name: Bahubali, Director: The film Bahubali is directed by Rajamouli

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
  con.query(sql);

  //insert data into created table
  sql = `INSERT INTO FILMS (NAME, DIRECTOR)
  VALUES (''RRR'', ''The film RRR is directed by Rajamouli''),
  (''Bahubali'', ''The film Bahubali is directed by Rajamouli''),
  (''Avatar'', ''The film Avatar is directed by James cameron''),
  (''Robot'', ''The film Robot is directed by Shankar'')`;
  con.query(sql);

  //display the table details!...
  sql = `SELECT * FROM FILMS WHERE MATCH (NAME, DIRECTOR) AGAINST (''Rajamouli'')`;
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

[
  {
    ID: 1,
    NAME: ''RRR'',
    DIRECTOR: ''The film RRR is directed by Rajamouli''
  },
  {
    ID: 2,
    NAME: ''Bahubali'',
    DIRECTOR: ''The film Bahubali is directed by Rajamouli''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class FulltextSearch {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //creating a table that takes fulltext column...!
         String sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
         statement.execute(sql);
         System.out.println("Table created successfully...!");

         //inserting data to the tables
         String insert = "INSERT INTO FILMS (NAME, DIRECTOR) VALUES (''RRR'', ''The film RRR is directed by Rajamouli''), (''Bahubali'', ''The film Bahubali is directed by Rajamouli'')," +
                 "(''Avatar'', ''The film Avatar is directed by James cameron''), (''Robot'', ''The film Robot is directed by Shankar'')";
         statement.execute(insert);
         System.out.println("Data inserted successfully...!");

         //displaying the table records...!
         ResultSet resultSet = statement.executeQuery("SELECT * FROM FILMS WHERE MATCH (NAME, DIRECTOR) AGAINST (''Rajamouli'')");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2)+ " "+resultSet.getString(3));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Connected successfully...!
Table created successfully...!
Data inserted successfully...!
1 RRR The film RRR is directed by Rajamouli
2 Bahubali The film Bahubali is directed by Rajamouli

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
fulltext_search_query = f"SELECT * FROM FILMS WHERE MATCH (NAME, DIRECTOR) AGAINST (''Rajamouli'');"
cursorObj.execute(fulltext_search_query)
# Fetching all the results
results = cursorObj.fetchall()
# Display the result
print("Full-text search results:")
for row in results:
    print(row)
cursorObj.close()
connection.close()

Full-text search results:
(1, ''RRR'', ''The film RRR is directed by Rajamouli'')
(2, ''Bahubali'', ''The film Bahubali is directed by Rajamouli'')