FUNCTIONS IN SQL
STRING FUNCTIONS
mysql> select char(65);
+----------+
| char(65) |
+----------+
|A |
+----------+
1 row in set (0.00 sec)
mysql> select concat('A','B','C');
+---------------------+
| concat('A','B','C') |
+---------------------+
| ABC |
+---------------------+
1 row in set (0.00 sec)
mysql> select concat(empname,empsal) from emp;
+-------------------------+
| concat(empname,empsal) |
+-------------------------+
| Tanishq99999.00 |
| Armaan98000.00 |
| Vedant99000.00 |
| Kshitij97000.00 |
| Anoushka Bansal97000.00 |
| Prasansha89000.00 |
| Vedant99800.00 |
| Vedant95000.00 |
+-------------------------+
8 rows in set (0.00 sec)
mysql> select lower('ABC');
+--------------+
| lower('ABC') |
+--------------+
| abc |
+--------------+
1 row in set (0.00 sec)
mysql> select lower(empname) from emp;
+-----------------+
| lower(empname) |
+-----------------+
| tanishq |
| armaan |
| vedant |
| kshitij |
| anoushka bansal |
| prasansha |
| vedant |
| vedant |
+-----------------+
8 rows in set (0.00 sec)
mysql> select lCASE(empname) from emp;
+-----------------+
| lCASE(empname) |
+-----------------+
| tanishq |
| armaan |
| vedant |
| kshitij |
| anoushka bansal |
| prasansha |
| vedant |
| vedant |
+-----------------+
8 rows in set (0.00 sec)
mysql> select lCASE('ABC');
+--------------+
| lCASE('ABC') |
+--------------+
| abc |
+--------------+
1 row in set (0.00 sec)
mysql> select UPPER('Abc');
+--------------+
| UPPER('Abc') |
+--------------+
| ABC |
+--------------+
1 row in set (0.00 sec)
mysql> select UPPER(empname) from emp;
+-----------------+
| UPPER(empname) |
+-----------------+
| TANISHQ |
| ARMAAN |
| VEDANT |
| KSHITIJ |
| ANOUSHKA BANSAL |
| PRASANSHA |
| VEDANT |
| VEDANT |
+-----------------+
8 rows in set (0.00 sec)
mysql> select Ucase(empname) from emp;
+-----------------+
| Ucase(empname) |
+-----------------+
| TANISHQ |
| ARMAAN |
| VEDANT |
| KSHITIJ |
| ANOUSHKA BANSAL |
| PRASANSHA |
| VEDANT |
| VEDANT |
+-----------------+
8 rows in set (0.00 sec)
mysql> select substring('ABCDEFGH',3,4);
+---------------------------+
| substring('ABCDEFGH',3,4) |
+---------------------------+
| CDEF |
+---------------------------+
1 row in set (0.00 sec)
mysql> select substr('ABCDEFGH',3,4);
+------------------------+
| substr('ABCDEFGH',3,4) |
+------------------------+
| CDEF |
+------------------------+
1 row in set (0.00 sec)
mysql> select mid('ABCDEFGH',3,4);
+---------------------+
| mid('ABCDEFGH',3,4) |
+---------------------+
| CDEF |
+---------------------+
1 row in set (0.00 sec)
mysql> select trim(' ABC ')
-> ;
+-------------------+
| trim(' ABC ') |
+-------------------+
| ABC |
+-------------------+
1 row in set (0.00 sec)
NUMERIC FUNCTIONS
mysql> select LENGTH(' ABC ');
+---------------------+
| LENGTH(' ABC ') |
+---------------------+
| 9|
+---------------------+
1 row in set (0.00 sec)
mysql> select LENGTH(TRIM(' ABC '));
+---------------------------+
| LENGTH(TRIM(' ABC ')) |
+---------------------------+
| 3|
+---------------------------+
1 row in set (0.00 sec)
mysql> select LENGTH(LTRIM(' ABC '));
+----------------------------+
| LENGTH(LTRIM(' ABC ')) |
+----------------------------+
| 6|
+----------------------------+
1 row in set (0.00 sec)
mysql> select LENGTH(LTRIM(' ABC '));
+-----------------------------+
| LENGTH(LTRIM(' ABC ')) |
+-----------------------------+
| 7|
+-----------------------------+
1 row in set (0.00 sec)
mysql> select LENGTH(RTRIM(' ABC '));
+-----------------------------+
| LENGTH(RTRIM(' ABC ')) |
+-----------------------------+
| 6|
+-----------------------------+
1 row in set (0.00 sec)
mysql> SELECT MOD(11,2);
+-----------+
| MOD(11,2) |
+-----------+
| 1|
+-----------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT POWER(2,3);
+------------+
| POWER(2,3) |
+------------+
| 8|
+------------+
1 row in set (0.00 sec)
mysql> SELECT POW(2,3);
+----------+
| POW(2,3) |
+----------+
| 8|
+----------+
1 row in set (0.00 sec)
mysql> SELECT ROUND(19.9876555,2);
+---------------------+
| ROUND(19.9876555,2) |
+---------------------+
| 19.99 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT ROUND(19.9876555,3);
+---------------------+
| ROUND(19.9876555,3) |
+---------------------+
| 19.988 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT TRUNCATE(19.9876555,3);
+------------------------+
| TRUNCATE(19.9876555,3) |
+------------------------+
| 19.987 |
+------------------------+
1 row in set (0.00 sec)
mysql> SELECT TRUNCATE(19.9876555,2);
+------------------------+
| TRUNCATE(19.9876555,2) |
+------------------------+
| 19.98 |
+------------------------+
1 row in set (0.00 sec)
mysql> SELECT TRUNCATE(19.9876555,-1);
+-------------------------+
| TRUNCATE(19.9876555,-1) |
+-------------------------+
| 10 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT TRUNCATE(19.9876555,-2);
+-------------------------+
| TRUNCATE(19.9876555,-2) |
+-------------------------+
| 0|
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT TRUNCATE(1998.76555,-3);
+-------------------------+
| TRUNCATE(1998.76555,-3) |
+-------------------------+
| 1000 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT TRUNCATE(1998.76555,-2);
+-------------------------+
| TRUNCATE(1998.76555,-2) |
+-------------------------+
| 1900 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT TRUNCATE(1998.76555,-1);
+-------------------------+
| TRUNCATE(1998.76555,-1) |
+-------------------------+
| 1990 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT SQRT(900);
+-----------+
| SQRT(900) |
+-----------+
| 30 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT SIGN(900);
+-----------+
| SIGN(900) |
+-----------+
| 1|
+-----------+
1 row in set (0.00 sec)
mysql> SELECT SIGN(-900);
+------------+
| SIGN(-900) |
+------------+
| -1 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT SIGN(0);
+---------+
| SIGN(0) |
+---------+
| 0|
+---------+
1 row in set (0.00 sec)
DATE AND TIME FUNCTIONS
mysql> SELECT curdate();
+------------+
| curdate() |
+------------+
| 2021-07-02 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT curdate();
+------------+
| curdate() |
+------------+
| 2021-07-02 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT current_date();
+----------------+
| current_date() |
+----------------+
| 2021-07-02 |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT current_date;
+--------------+
| current_date |
+--------------+
| 2021-07-02 |
+--------------+
1 row in set (0.00 sec)
mysql> SELECT now();
+---------------------+
| now() |
+---------------------+
| 2021-07-02 09:36:54 |
+---------------------+
1 row in set (0.00 sec)
mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2021-07-02 |
+-------------+
1 row in set (0.00 sec)
mysql> select day(now());
+------------+
| day(now()) |
+------------+
| 2|
+------------+
1 row in set (0.00 sec)
mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
| 7|
+--------------+
1 row in set (0.00 sec)
mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
| 2021 |
+-------------+
1 row in set (0.00 sec)
mysql> select hour(now());
+-------------+
| hour(now()) |
+-------------+
| 9|
+-------------+
1 row in set (0.00 sec)
mysql> select minute(now());
+---------------+
| minute(now()) |
+---------------+
| 37 |
+---------------+
1 row in set (0.00 sec)
mysql> select second(now());
+---------------+
| second(now()) |
+---------------+
| 2|
+---------------+
1 row in set (0.00 sec)
mysql> select sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2021-07-02 09:38:22 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT DATEDIFF('2000-09-09',CURDATE());
+----------------------------------+
| DATEDIFF('2000-09-09',CURDATE()) |
+----------------------------------+
| -7600 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATEDIFF('2000-09-09',CURDATE())/365;
+--------------------------------------+
| DATEDIFF('2000-09-09',CURDATE())/365 |
+--------------------------------------+
| -20.8219 |
+--------------------------------------+
1 row in set (0.00 sec)
