Scribd
Upload
7 views30 pages

MySQL Random Number and String Functions

The document contains a series of MySQL queries demonstrating the use of the RAND() function to generate random numbers, string concatenation with CONCAT and CONCAT_WS, string manipulation functions like UPPER, LOWER, and TRIM, and aggregate functions such as COUNT, AVG, and MAX. It also shows how to format and manipulate employee data from a table named 'emp'. The queries illustrate various operations on strings and numerical data, including generating random values and formatting output.

Uploaded by

ramesh
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
7 views30 pages

MySQL Random Number and String Functions

The document contains a series of MySQL queries demonstrating the use of the RAND() function to generate random numbers, string concatenation with CONCAT and CONCAT_WS, string manipulation functions like UPPER, LOWER, and TRIM, and aggregate functions such as COUNT, AVG, and MAX. It also shows how to format and manipulate employee data from a table named 'emp'. The queries illustrate various operations on strings and numerical data, including generating random values and formatting output.

Uploaded by

ramesh
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

mysql> select rand();

+--------------------+

| rand() |

+--------------------+

| 0.5781547302176754 |

+--------------------+

1 row in set (0.24 sec)

mysql> select rand();

+---------------------+

| rand() |

+---------------------+

| 0.18997477944006658 |

+---------------------+

1 row in set (0.00 sec)

mysql> select rand();

+---------------------+

| rand() |

+---------------------+

| 0.21540973728095156 |

+---------------------+

1 row in set (0.00 sec)

mysql> select rand();

+--------------------+

| rand() |

+--------------------+

| 0.5071243788182032 |

+--------------------+

1 row in set (0.00 sec)


mysql> select rand();

+--------------------+

| rand() |

+--------------------+

| 0.8893927129818059 |

+--------------------+

1 row in set (0.00 sec)

mysql> select rand();

+-------------------+

| rand() |

+-------------------+

| 0.925590459188065 |

+-------------------+

1 row in set (0.00 sec)

mysql> select rand()*1000;

+-------------------+

| rand()*1000 |

+-------------------+

| 959.7741877285523 |

+-------------------+

1 row in set (0.01 sec)

mysql> select rand()*1000;

+-------------------+

| rand()*1000 |

+-------------------+

| 22.09959181221164 |

+-------------------+
1 row in set (0.00 sec)

mysql> select rand()*1000;

+-------------------+

| rand()*1000 |

+-------------------+

| 231.1754266090462 |

+-------------------+

1 row in set (0.00 sec)

mysql> select rand()*1000;

+-------------------+

| rand()*1000 |

+-------------------+

| 89.57838834224118 |

+-------------------+

1 row in set (0.00 sec)

mysql> select rand()*1000;

+-------------------+

| rand()*1000 |

+-------------------+

| 754.3656926177123 |

+-------------------+

1 row in set (0.00 sec)

mysql> select rand()*1000;

+--------------------+

| rand()*1000 |

+--------------------+

| 503.09332879548276 |
+--------------------+

1 row in set (0.00 sec)

mysql> select rand()*1000;

+--------------------+

| rand()*1000 |

+--------------------+

| 252.36959685792175 |

+--------------------+

1 row in set (0.00 sec)

mysql> select rand()*1000;

+-------------------+

| rand()*1000 |

+-------------------+

| 752.5680286368057 |

+-------------------+

1 row in set (0.00 sec)

mysql> select rand()*10000;

+--------------------+

| rand()*10000 |

+--------------------+

| 57.313833439083616 |

+--------------------+

1 row in set (0.00 sec)

mysql> select rand()*10000;

+--------------------+

| rand()*10000 |

+--------------------+
| 7709.5286154276955 |

+--------------------+

1 row in set (0.00 sec)

mysql> select rand()*10000;

+-------------------+

| rand()*10000 |

+-------------------+

| 8375.701884157677 |

+-------------------+

1 row in set (0.00 sec)

mysql> select ceil(rand()*10000);

+--------------------+

| ceil(rand()*10000) |

+--------------------+

| 8750 |

+--------------------+

1 row in set (0.00 sec)

mysql> select ceil(rand()*10000);

+--------------------+

| ceil(rand()*10000) |

+--------------------+

| 8623 |

+--------------------+

1 row in set (0.00 sec)

mysql> select ceil(rand()*10000);

+--------------------+

| ceil(rand()*10000) |
+--------------------+

| 6863 |

+--------------------+

1 row in set (0.00 sec)

mysql> select ceil(rand()*1000000);

+----------------------+

| ceil(rand()*1000000) |

+----------------------+

| 844646 |

+----------------------+

1 row in set (0.00 sec)

mysql> select ceil(rand()*1000000);

+----------------------+

| ceil(rand()*1000000) |

+----------------------+

| 164387 |

+----------------------+

1 row in set (0.00 sec)

mysql> select ceil(rand()*1000000);

+----------------------+

| ceil(rand()*1000000) |

+----------------------+

| 287999 |

+----------------------+

1 row in set (0.00 sec)

mysql> SELECT deptno,

-> COUNT(*) as employee_count,


-> AVG(sal) as avg_salary,

-> MAX(sal) as max_salary

-> FROM emp

-> GROUP BY deptno

-> HAVING COUNT(*) > 5;

+--------+----------------+--------------+------------+

| deptno | employee_count | avg_salary | max_salary |

+--------+----------------+--------------+------------+

| 30 | 6 | 15516.666667 | 28050.00 |

+--------+----------------+--------------+------------+

1 row in set (0.03 sec)

mysql> select concat("Core","Java");

+-----------------------+

| concat("Core","Java") |

+-----------------------+

| CoreJava |

+-----------------------+

1 row in set (0.00 sec)

mysql> select concat("Core","Java","Spring");

+--------------------------------+

| concat("Core","Java","Spring") |

+--------------------------------+

| CoreJavaSpring |

+--------------------------------+

1 row in set (0.00 sec)

mysql> select concat("Core","Java") as full;

+----------+

| full |
+----------+

| CoreJava |

+----------+

1 row in set, 1 warning (0.01 sec)

mysql> select concat("Core","Java") as full_name;

+-----------+

| full_name |

+-----------+

| CoreJava |

+-----------+

1 row in set (0.00 sec)

mysql> select concat(ename,' Working as a ',job) from emp;

+------------------------------------+

| concat(ename,' Working as a ',job) |

+------------------------------------+

| SMITH Working as a CLERK |

| ALLEN Working as a SALESMAN |

| WARD Working as a SALESMAN |

| JONES Working as a MANAGER |

| MARTIN Working as a SALESMAN |

| BLAKE Working as a MANAGER |

| CLARK Working as a MANAGER |

| SCOTT Working as a ANALYST |

| KING Working as a PRESIDENT |

| TURNER Working as a SALESMAN |

| ADAMS Working as a CLERK |

| JAMES Working as a CLERK |

| FORD Working as a ANALYST |

| MILLER Working as a CLERK |


+------------------------------------+

14 rows in set (0.00 sec)

mysql> select concat_ws('|',ename,job,sal) from emp;

+------------------------------+

| concat_ws('|',ename,job,sal) |

+------------------------------+

| SMITH|CLERK|8000.00 |

| ALLEN|SALESMAN|16000.00 |

| WARD|SALESMAN|12500.00 |

| JONES|MANAGER|29075.00 |

| MARTIN|SALESMAN|12050.00 |

| BLAKE|MANAGER|28050.00 |

| CLARK|MANAGER|24050.00 |

| SCOTT|ANALYST|30000.00 |

| KING|PRESIDENT|50000.00 |

| TURNER|SALESMAN|15000.00 |

| ADAMS|CLERK|11000.00 |

| JAMES|CLERK|9500.00 |

| FORD|ANALYST|30000.00 |

| MILLER|CLERK|13000.00 |

+------------------------------+

14 rows in set (0.00 sec)

mysql> select concat_ws(' | ',ename,job,sal) from emp;

+--------------------------------+

| concat_ws(' | ',ename,job,sal) |

+--------------------------------+

| SMITH | CLERK | 8000.00 |

| ALLEN | SALESMAN | 16000.00 |

| WARD | SALESMAN | 12500.00 |


| JONES | MANAGER | 29075.00 |

| MARTIN | SALESMAN | 12050.00 |

| BLAKE | MANAGER | 28050.00 |

| CLARK | MANAGER | 24050.00 |

| SCOTT | ANALYST | 30000.00 |

| KING | PRESIDENT | 50000.00 |

| TURNER | SALESMAN | 15000.00 |

| ADAMS | CLERK | 11000.00 |

| JAMES | CLERK | 9500.00 |

| FORD | ANALYST | 30000.00 |

| MILLER | CLERK | 13000.00 |

+--------------------------------+

14 rows in set (0.00 sec)

mysql> select upper("prr");

+--------------+

| upper("prr") |

+--------------+

| PRR |

+--------------+

1 row in set (0.00 sec)

mysql> select ucase("prr");

+--------------+

| ucase("prr") |

+--------------+

| PRR |

+--------------+

1 row in set (0.00 sec)

mysql> select lower(ename) from emp;


+--------------+

| lower(ename) |

+--------------+

| smith |

| allen |

| ward |

| jones |

| martin |

| blake |

| clark |

| scott |

| king |

| turner |

| adams |

| james |

| ford |

| miller |

+--------------+

14 rows in set (0.00 sec)

mysql> select lcase(ename) from emp;

+--------------+

| lcase(ename) |

+--------------+

| smith |

| allen |

| ward |

| jones |

| martin |

| blake |

| clark |
| scott |

| king |

| turner |

| adams |

| james |

| ford |

| miller |

+--------------+

14 rows in set (0.00 sec)

mysql> select ename,length(ename) from emp;

+--------+---------------+

| ename | length(ename) |

+--------+---------------+

| SMITH | 5|

| ALLEN | 5|

| WARD | 4|

| JONES | 5|

| MARTIN | 6|

| BLAKE | 5|

| CLARK | 5|

| SCOTT | 5|

| KING | 4|

| TURNER | 6|

| ADAMS | 5|

| JAMES | 5|

| FORD | 4|

| MILLER | 6|

+--------+---------------+

14 rows in set (0.00 sec)


mysql> select ename, char_length(ename) from emp;

+--------+--------------------+

| ename | char_length(ename) |

+--------+--------------------+

| SMITH | 5|

| ALLEN | 5|

| WARD | 4|

| JONES | 5|

| MARTIN | 6|

| BLAKE | 5|

| CLARK | 5|

| SCOTT | 5|

| KING | 4|

| TURNER | 6|

| ADAMS | 5|

| JAMES | 5|

| FORD | 4|

| MILLER | 6|

+--------+--------------------+

14 rows in set (0.00 sec)

mysql> select substring(ename,1,3);

ERROR 1054 (42S22): Unknown column 'ename' in 'field list'

mysql> select substring(ename,1,3) from emp;

+----------------------+

| substring(ename,1,3) |

+----------------------+

| SMI |

| ALL |

| WAR |

| JON |
| MAR |

| BLA |

| CLA |

| SCO |

| KIN |

| TUR |

| ADA |

| JAM |

| FOR |

| MIL |

+----------------------+

14 rows in set (0.00 sec)

mysql> select left(ename,2) from emp;

+---------------+

| left(ename,2) |

+---------------+

| SM |

| AL |

| WA |

| JO |

| MA |

| BL |

| CL |

| SC |

| KI |

| TU |

| AD |

| JA |

| FO |

| MI |
+---------------+

14 rows in set (0.00 sec)

mysql> select left(ename,3) from emp;

+---------------+

| left(ename,3) |

+---------------+

| SMI |

| ALL |

| WAR |

| JON |

| MAR |

| BLA |

| CLA |

| SCO |

| KIN |

| TUR |

| ADA |

| JAM |

| FOR |

| MIL |

+---------------+

14 rows in set (0.00 sec)

mysql> select right(ename,3) from emp;

+----------------+

| right(ename,3) |

+----------------+

| ITH |

| LEN |

| ARD |
| NES |

| TIN |

| AKE |

| ARK |

| OTT |

| ING |

| NER |

| AMS |

| MES |

| ORD |

| LER |

+----------------+

14 rows in set (0.00 sec)

mysql> select right(ename,2) from emp;

+----------------+

| right(ename,2) |

+----------------+

| TH |

| EN |

| RD |

| ES |

| IN |

| KE |

| RK |

| TT |

| NG |

| ER |

| MS |

| ES |

| RD |
| ER |

+----------------+

14 rows in set (0.00 sec)

mysql> select lpad(ename,10,"x") from emp;

+--------------------+

| lpad(ename,10,"x") |

+--------------------+

| xxxxxSMITH |

| xxxxxALLEN |

| xxxxxxWARD |

| xxxxxJONES |

| xxxxMARTIN |

| xxxxxBLAKE |

| xxxxxCLARK |

| xxxxxSCOTT |

| xxxxxxKING |

| xxxxTURNER |

| xxxxxADAMS |

| xxxxxJAMES |

| xxxxxxFORD |

| xxxxMILLER |

+--------------------+

14 rows in set (0.00 sec)

mysql> select rpad(ename,10,"x") from emp;

+--------------------+

| rpad(ename,10,"x") |

+--------------------+

| SMITHxxxxx |

| ALLENxxxxx |
| WARDxxxxxx |

| JONESxxxxx |

| MARTINxxxx |

| BLAKExxxxx |

| CLARKxxxxx |

| SCOTTxxxxx |

| KINGxxxxxx |

| TURNERxxxx |

| ADAMSxxxxx |

| JAMESxxxxx |

| FORDxxxxxx |

| MILLERxxxx |

+--------------------+

14 rows in set (0.00 sec)

mysql> select ltrim(" Hello");

+---------------------+

| ltrim(" Hello") |

+---------------------+

| Hello |

+---------------------+

1 row in set (0.00 sec)

mysql> select ltrim(" Hello ");

+---------------------------+

| ltrim(" Hello ") |

+---------------------------+

| Hello |

+---------------------------+

1 row in set (0.00 sec)


mysql> select rtrim(" Hello ");

+---------------------------+

| rtrim(" Hello ") |

+---------------------------+

| Hello |

+---------------------------+

1 row in set (0.00 sec)

mysql> select trim(" Hello ");

+--------------------------+

| trim(" Hello ") |

+--------------------------+

| Hello |

+--------------------------+

1 row in set (0.00 sec)

mysql> select replace(ename,"MANAGER","ADMIN") from emp;

+----------------------------------+

| replace(ename,"MANAGER","ADMIN") |

+----------------------------------+

| SMITH |

| ALLEN |

| WARD |

| JONES |

| MARTIN |

| BLAKE |

| CLARK |

| SCOTT |

| KING |

| TURNER |

| ADAMS |
| JAMES |

| FORD |

| MILLER |

+----------------------------------+

14 rows in set (0.00 sec)

mysql> select replace(job,"MANAGER","ADMIN") from emp;

+--------------------------------+

| replace(job,"MANAGER","ADMIN") |

+--------------------------------+

| CLERK |

| SALESMAN |

| SALESMAN |

| ADMIN |

| SALESMAN |

| ADMIN |

| ADMIN |

| ANALYST |

| PRESIDENT |

| SALESMAN |

| CLERK |

| CLERK |

| ANALYST |

| CLERK |

+--------------------------------+

14 rows in set (0.00 sec)

mysql> select insert(ename,1,3,"XYZ") from emp;

+-------------------------+

| insert(ename,1,3,"XYZ") |

+-------------------------+
| XYZTH |

| XYZEN |

| XYZD |

| XYZES |

| XYZTIN |

| XYZKE |

| XYZRK |

| XYZTT |

| XYZG |

| XYZNER |

| XYZMS |

| XYZES |

| XYZD |

| XYZLER |

+-------------------------+

14 rows in set (0.00 sec)

mysql> select insert(ename,0,3,"XYZ") from emp;

+-------------------------+

| insert(ename,0,3,"XYZ") |

+-------------------------+

| SMITH |

| ALLEN |

| WARD |

| JONES |

| MARTIN |

| BLAKE |

| CLARK |

| SCOTT |

| KING |

| TURNER |
| ADAMS |

| JAMES |

| FORD |

| MILLER |

+-------------------------+

14 rows in set (0.00 sec)

mysql> select insert(ename,1,3,"XYZ") from emp;

+-------------------------+

| insert(ename,1,3,"XYZ") |

+-------------------------+

| XYZTH |

| XYZEN |

| XYZD |

| XYZES |

| XYZTIN |

| XYZKE |

| XYZRK |

| XYZTT |

| XYZG |

| XYZNER |

| XYZMS |

| XYZES |

| XYZD |

| XYZLER |

+-------------------------+

14 rows in set (0.00 sec)

mysql> SELECT ename, LOCATE('A', ename) AS a_position FROM emp;

+--------+------------+

| ename | a_position |
+--------+------------+

| SMITH | 0|

| ALLEN | 1|

| WARD | 2|

| JONES | 0|

| MARTIN | 2|

| BLAKE | 3|

| CLARK | 3|

| SCOTT | 0|

| KING | 0|

| TURNER | 0|

| ADAMS | 1|

| JAMES | 2|

| FORD | 0|

| MILLER | 0|

+--------+------------+

14 rows in set (0.00 sec)

mysql> SELECT ename, LOCATE('FORD', ename) AS a_position FROM emp;

+--------+------------+

| ename | a_position |

+--------+------------+

| SMITH | 0|

| ALLEN | 0|

| WARD | 0|

| JONES | 0|

| MARTIN | 0|

| BLAKE | 0|

| CLARK | 0|

| SCOTT | 0|

| KING | 0|
| TURNER | 0|

| ADAMS | 0|

| JAMES | 0|

| FORD | 1|

| MILLER | 0|

+--------+------------+

14 rows in set (0.00 sec)

mysql> SELECT job, LOCATE('SALES', job) AS a_position FROM emp;

+-----------+------------+

| job | a_position |

+-----------+------------+

| CLERK | 0|

| SALESMAN | 1|

| SALESMAN | 1|

| MANAGER | 0|

| SALESMAN | 1|

| MANAGER | 0|

| MANAGER | 0|

| ANALYST | 0|

| PRESIDENT | 0|

| SALESMAN | 1|

| CLERK | 0|

| CLERK | 0|

| ANALYST | 0|

| CLERK | 0|

+-----------+------------+

14 rows in set (0.00 sec)

mysql> SELECT ename FROM emp WHERE ename LIKE 'S%';

+-------+
| ename |

+-------+

| SMITH |

| SCOTT |

+-------+

2 rows in set (0.01 sec)

mysql> SELECT ename FROM emp WHERE ename LIKE 'SMITH';

+-------+

| ename |

+-------+

| SMITH |

+-------+

1 row in set (0.00 sec)

mysql> SELECT ename FROM emp WHERE ename LIKE 'SM%';

+-------+

| ename |

+-------+

| SMITH |

+-------+

1 row in set (0.00 sec)

mysql> SELECT ename FROM emp WHERE ename LIKE '%M';

Empty set (0.00 sec)

mysql> SELECT ename FROM emp WHERE ename LIKE '%H';

+-------+

| ename |

+-------+

| SMITH |
+-------+

1 row in set (0.00 sec)

mysql> SELECT ename FROM emp WHERE ename LIKE '%N';

+--------+

| ename |

+--------+

| ALLEN |

| MARTIN |

+--------+

2 rows in set (0.00 sec)

mysql> select sal from emp;

+----------+

| sal |

+----------+

| 8000.00 |

| 16000.00 |

| 12500.00 |

| 29075.00 |

| 12050.00 |

| 28050.00 |

| 24050.00 |

| 30000.00 |

| 50000.00 |

| 15000.00 |

| 11000.00 |

| 9500.00 |

| 30000.00 |

| 13000.00 |

+----------+
14 rows in set (0.00 sec)

mysql> SELECT ename, FORMAT(sal, 0) AS formatted_salary FROM emp;

+--------+------------------+

| ename | formatted_salary |

+--------+------------------+

| SMITH | 8,000 |

| ALLEN | 16,000 |

| WARD | 12,500 |

| JONES | 29,075 |

| MARTIN | 12,050 |

| BLAKE | 28,050 |

| CLARK | 24,050 |

| SCOTT | 30,000 |

| KING | 50,000 |

| TURNER | 15,000 |

| ADAMS | 11,000 |

| JAMES | 9,500 |

| FORD | 30,000 |

| MILLER | 13,000 |

+--------+------------------+

14 rows in set (0.00 sec)

mysql> SELECT ename, FORMAT(sal, 1) AS formatted_salary FROM emp;

+--------+------------------+

| ename | formatted_salary |

+--------+------------------+

| SMITH | 8,000.0 |

| ALLEN | 16,000.0 |

| WARD | 12,500.0 |

| JONES | 29,075.0 |
| MARTIN | 12,050.0 |

| BLAKE | 28,050.0 |

| CLARK | 24,050.0 |

| SCOTT | 30,000.0 |

| KING | 50,000.0 |

| TURNER | 15,000.0 |

| ADAMS | 11,000.0 |

| JAMES | 9,500.0 |

| FORD | 30,000.0 |

| MILLER | 13,000.0 |

+--------+------------------+

14 rows in set (0.00 sec)

mysql> SELECT ename, FORMAT(sal, 2) AS formatted_salary FROM emp;

+--------+------------------+

| ename | formatted_salary |

+--------+------------------+

| SMITH | 8,000.00 |

| ALLEN | 16,000.00 |

| WARD | 12,500.00 |

| JONES | 29,075.00 |

| MARTIN | 12,050.00 |

| BLAKE | 28,050.00 |

| CLARK | 24,050.00 |

| SCOTT | 30,000.00 |

| KING | 50,000.00 |

| TURNER | 15,000.00 |

| ADAMS | 11,000.00 |

| JAMES | 9,500.00 |

| FORD | 30,000.00 |

| MILLER | 13,000.00 |
+--------+------------------+

14 rows in set (0.00 sec)

mysql> select reverse(ename) from emp;

+----------------+

| reverse(ename) |

+----------------+

| HTIMS |

| NELLA |

| DRAW |

| SENOJ |

| NITRAM |

| EKALB |

| KRALC |

| TTOCS |

| GNIK |

| RENRUT |

| SMADA |

| SEMAJ |

| DROF |

| RELLIM |

+----------------+

14 rows in set (0.00 sec)

mysql> SELECT ename FROM emp WHERE ename REGEXP '^A';

+-------+

| ename |

+-------+

| ALLEN |

| ADAMS |

+-------+
2 rows in set (0.03 sec)

You might also like