Can I write my own MySQL functions to use in MySQL queries?

MySQLMySQLi Database

Yes, you can write own MySQL function to use in MySQL queries. Following is the syntax:

DELIMITER //
CREATE FUNCTION yourFunctionName(optional parameters)) RETURNS yourDataType
DETERMINISTIC NO SQL
BEGIN
yourStatements1
.
.
.
.
N
END
//
DELIMITER ;

We have used the CREATE FUNCTION above to create a custom function.

Let us create a custom MySQL function to use in MySQL query:

mysql> DELIMITER //
mysql> CREATE FUNCTION get_First_Name(Name VARCHAR(255)) RETURNS
VARCHAR(255)
   DETERMINISTIC
   NO SQL
   BEGIN
      RETURN LEFT(Name,LOCATE(' ',Name) - 1);
   END
   //
Query OK, 0 rows affected (0.20 sec)
mysql> DELIMITER ;

Now call the above function using SELECT statement:

mysql> select get_First_Name('David Miller');

This will produce the following output:

+--------------------------------+
| get_First_Name('David Miller') |
+--------------------------------+
| David                          |
+--------------------------------+
1 row in set, 2 warnings (0.00 sec)
raja
Published on 29-Apr-2019 12:36:17
Advertisements