MySQL Function: GROUP_CONCAT

Don’t know why I never thought to google for something like this before. There probably aren’t too many good uses for it, but it saved me a bunch of time today so I thought I’d share:

GROUP_CONCAT

Running a query like the one below, would return all the values from my sub-select as a comma delimited list so this…

SELECT products.namename,
        GROUP_CONCAT(options.option) AS options
FROM products
     INNER JOIN options
     ON          options.productID = products.productID

Could return something like this:

NAME	OPTIONS
t-shirt	green, blue, red, small, medium, large
hat	cowboy

MySQL Capitalize Function

I wrote a little MySQL function to capitalize the first letter of every word in a string. I thought I’d share since I wasn’t able to google for it.

CREATE FUNCTION CAP_FIRST (input VARCHAR(255))

RETURNS VARCHAR(255)

DETERMINISTIC

BEGIN
 DECLARE len INT;
 DECLARE i INT;

 SET len   = CHAR_LENGTH(input);
 SET input = LOWER(input);
 SET i = 0;

 WHILE (i < len) DO
  IF (MID(input,i,1) = ' ' OR i = 0) THEN
   IF (i < len) THEN
    SET input = CONCAT(
     LEFT(input,i),
     UPPER(MID(input,i + 1,1)),
     RIGHT(input,len - i - 1)
    );
   END IF;
  END IF;
  SET i = i + 1;
 END WHILE;

 RETURN input;
END;

• 

So running the following code...

SELECT CAP_FIRST(
 'this is totally like   @ TEST 1 right!' 
)

Returns the string "This Is Totally Like @ Test 1 Right!"

I would rather have regex'd it, but I couldn't find any sort of regex replace function in the docs.

Rank function in MySQL

I am not an expert in MySQL . I need to find out rank of customers. 

Here I am adding the corresponding ANSI standard SQL query for my requirement. 

Please help me to convert it to MySQL .

 SELECT RANK() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender], 
   FirstName, 
   Age,
   Gender 
 FROM Person

Is there any function to find out rank in MySQL?

 Answers

One option is to use a ranking variable, such as the following:

SELECT    first_name,
          age,
          gender,
          @curRank := @curRank + 1 AS rank
FROM      person p, (SELECT @curRank := 0) r
ORDER BY  age;

The (SELECT @curRank := 0) part allows the variable initialization without requiring 

a separate SET command.

Test case:

CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));

INSERT INTO person VALUES (1, 'Bob', 25, 'M');
INSERT INTO person VALUES (2, 'Jane', 20, 'F');
INSERT INTO person VALUES (3, 'Jack', 30, 'M');
INSERT INTO person VALUES (4, 'Bill', 32, 'M');
INSERT INTO person VALUES (5, 'Nick', 22, 'M');
INSERT INTO person VALUES (6, 'Kathy', 18, 'F');
INSERT INTO person VALUES (7, 'Steve', 36, 'M');
INSERT INTO person VALUES (8, 'Anne', 25, 'F');

Result:

+------------+------+--------+------+
| first_name | age  | gender | rank |
+------------+------+--------+------+
| Kathy      |   18 | F      |    1 |
| Jane       |   20 | F      |    2 |
| Nick       |   22 | M      |    3 |
| Bob        |   25 | M      |    4 |
| Anne       |   25 | F      |    5 |
| Jack       |   30 | M      |    6 |
| Bill       |   32 | M      |    7 |
| Steve      |   36 | M      |    8 |
+------------+------+--------+------+
8 rows in set (0.02 sec)

---

While the most upvoted answer ranks, it doesn't partition, You can do a self Join to 

get the whole thing partitioned also:

SELECT    a.first_name,
      a.age,
      a.gender,
        count(b.age)+1 as rank
FROM  person a left join person b on a.age>b.age and a.gender=b.gender 
group by  a.first_name,
      a.age,
      a.gender

Use Case

CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));

INSERT INTO person VALUES (1, 'Bob', 25, 'M');
INSERT INTO person VALUES (2, 'Jane', 20, 'F');
INSERT INTO person VALUES (3, 'Jack', 30, 'M');
INSERT INTO person VALUES (4, 'Bill', 32, 'M');
INSERT INTO person VALUES (5, 'Nick', 22, 'M');
INSERT INTO person VALUES (6, 'Kathy', 18, 'F');
INSERT INTO person VALUES (7, 'Steve', 36, 'M');
INSERT INTO person VALUES (8, 'Anne', 25, 'F');

Answer:

Bill    32  M   4
Bob     25  M   2
Jack    30  M   3
Nick    22  M   1
Steve   36  M   5
Anne    25  F   3
Jane    20  F   2
Kathy   18  F   1

---

Combination of Daniel's and Salman's answer. However the rank will not give as 

continues sequence with ties exists . Instead it skips the rank to next. So maximum 

always reach row count.

    SELECT    first_name,
              age,
              gender,
              IF(age=@_last_age,@curRank:=@curRank,@curRank:=@_sequence) AS rank,
              @_sequence:=@_sequence+1,@_last_age:=age
    FROM      person p, (SELECT @curRank := 1, @_sequence:=1, @_last_age:=0) r
    ORDER BY  age;

Schema and Test Case:

CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));

INSERT INTO person VALUES (1, 'Bob', 25, 'M');
INSERT INTO person VALUES (2, 'Jane', 20, 'F');
INSERT INTO person VALUES (3, 'Jack', 30, 'M');
INSERT INTO person VALUES (4, 'Bill', 32, 'M');
INSERT INTO person VALUES (5, 'Nick', 22, 'M');
INSERT INTO person VALUES (6, 'Kathy', 18, 'F');
INSERT INTO person VALUES (7, 'Steve', 36, 'M');
INSERT INTO person VALUES (8, 'Anne', 25, 'F');
INSERT INTO person VALUES (9, 'Kamal', 25, 'M');
INSERT INTO person VALUES (10, 'Saman', 32, 'M');

Output:

+------------+------+--------+------+--------------------------+-----------------+
| first_name | age  | gender | rank | @_sequence:=@_sequence+1 | @_last_age:=age |
+------------+------+--------+------+--------------------------+-----------------+
| Kathy      |   18 | F      |    1 |                        2 |              18 |
| Jane       |   20 | F      |    2 |                        3 |              20 |
| Nick       |   22 | M      |    3 |                        4 |              22 |
| Kamal      |   25 | M      |    4 |                        5 |              25 |
| Anne       |   25 | F      |    4 |                        6 |              25 |
| Bob        |   25 | M      |    4 |                        7 |              25 |
| Jack       |   30 | M      |    7 |                        8 |              30 |
| Bill       |   32 | M      |    8 |                        9 |              32 |
| Saman      |   32 | M      |    8 |                       10 |              32 |
| Steve      |   36 | M      |   10 |                       11 |              36 |
+------------+------+--------+------+--------------------------+-----------------+

---

If you want to rank just one person you can do the following:

SELECT COUNT(Age) + 1
 FROM PERSON
WHERE(Age < age_to_rank)

This ranking corresponds to the oracle RANK function (Where if you have people with the 

same age they get the same rank, and the ranking after that is non-consecutive).

It's a little bit faster than using one of the above solutions in a sub query and selecting from

 that to get the ranking of one person.

This can be used to rank everyone but it's slower than the above solutions.

SELECT
  Age AS age_var,
(
  SELECT COUNT(Age) + 1
  FROM Person
  WHERE (Age < age_var)
 ) AS rank
 FROM Person

List of Stored Procedures/Functions Mysql Command Line

How can I see the list of the stored procedures or stored functions in mysql command line like show tables; or show databases; commands.

 Answers

SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;

---

For view procedure in name wise

select name from mysql.proc 

below code used to list all the procedure and below code is give same result as show procedure status

select * from mysql.proc 

---

As mentioned above,

show procedure status;

Will indeed show a list of procedures, but shows all of them, server-wide.

If you want to see just the ones in a single database, try this:

SHOW PROCEDURE STATUS WHERE Db = 'databasename';

---

My preference is for something that:

1. Lists both functions and procedures,
2. Lets me know which are which,
3. Gives the procedures' names and types and nothing else,
4. Filters results by the current database, not the current definer
5. Sorts the result

Stitching together from other answers in this thread, I end up with

select 
  name, type 
from 
  mysql.proc 
where 
  db = database() 
order by 
  type, name;

... which ends you up with results that look like this:

mysql> select name, type from mysql.proc where db = database() order by type, name;
+------------------------------+-----------+
| name                         | type      |
+------------------------------+-----------+
| get_oldest_to_scan           | FUNCTION  |
| get_language_prevalence      | PROCEDURE |
| get_top_repos_by_user        | PROCEDURE |
| get_user_language_prevalence | PROCEDURE |
+------------------------------+-----------+
4 rows in set (0.30 sec)

---

To show just yours:

SELECT
  db, type, specific_name, param_list, returns
FROM
  mysql.proc
WHERE
  definer LIKE
  CONCAT('%', CONCAT((SUBSTRING_INDEX((SELECT user()), '@', 1)), '%'));

---

SELECT specific_name FROM `information_schema`.`ROUTINES` WHERE routine_schema='database_name'

---

                           show procedure status;

using this command you can see the all procedures in databases

---

Use the following query for all the procedures:

select * from sysobjects 
where type='p'
order by crdate desc

MySQL functions

In this part of the MySQL tutorial, we will cover MySQL built-in functions.

MySQL built-in functions can be categorised into several groups.

• Mathematical functions
• Aggregate functions
• String functions
• Date and time functions
• System Functions

Here we show only a portion of all MySQL functions. To get the full list of available functions, consult the MySQL reference manual.

Mathematical functions

MySQL supports multiple mathematical functions.

mysql> SELECT RAND();
+-------------------+
| RAND()            |
+-------------------+
| 0.786536605829873 |
+-------------------+

The RAND() function returns a random number from the <0, 1> interval.

mysql> SELECT ABS(-3), PI(), SIN(0.5);
+---------+----------+-------------------+
| ABS(-3) | PI()     | SIN(0.5)          |
+---------+----------+-------------------+
|       3 | 3.141593 | 0.479425538604203 |
+---------+----------+-------------------+

The ABS() function returns the absolute value of a number. The PI() function gives the value of PI. And the SIN() function computes the sine of an argument.

mysql> SELECT BIN(22), OCT(22), HEX(22);
+---------+---------+---------+
| BIN(22) | OCT(22) | HEX(22) |
+---------+---------+---------+
| 10110   | 26      | 16      |
+---------+---------+---------+

We use functions to give binary, octal and hexadecimal representation of decimal 22.

mysql> SELECT CEIL(11.256), FLOOR(11.256), ROUND(11.256, 2);
+--------------+---------------+------------------+
| CEIL(11.256) | FLOOR(11.256) | ROUND(11.256, 2) |
+--------------+---------------+------------------+
|           12 |            11 |            11.26 |
+--------------+---------------+------------------+

The CEIL() function rounds the value to the smallest following integer. The FLOOR() function rounds the value to the largest previous integer. The ROUND() returns a number rounded to a specified number of decimal places.

mysql> SELECT POW(3, 3), SQRT(9);
+-----------+---------+
| POW(3, 3) | SQRT(9) |
+-----------+---------+
|        27 |       3 |
+-----------+---------+

The power and the square root functions.

mysql> SELECT DEGREES(2*PI());
+-----------------+
| DEGREES(2*PI()) |
+-----------------+
|             360 |
+-----------------+

The DEGREES() function computes degrees from radians.

Aggregate functions

Aggregate functions operate on sets of values.

mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id | Name       | Cost   |
+----+------------+--------+
|  1 | Audi       |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda      |   9000 |
|  4 | Volvo      |  29000 |
|  5 | Bentley    | 350000 |
|  6 | Citroen    |  21000 |
|  7 | Hummer     |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+

We have the Cars table.

mysql> SELECT MIN(Cost), MAX(Cost), AVG(Cost)
    -> FROM Cars;
+-----------+-----------+------------+
| MIN(Cost) | MAX(Cost) | AVG(Cost)  |
+-----------+-----------+------------+
|      9000 |    350000 | 72721.1250 |
+-----------+-----------+------------+

We use the MIN(), MAX() and AVG() aggregate functions to compute the minimal price, maximal price and the average price of cars in the table.

mysql> SELECT SUM(Cost), COUNT(Id), STD(Cost), 
    -> VARIANCE(Cost) FROM Cars;
+-----------+-----------+-------------+------------------+
| SUM(Cost) | COUNT(Id) | STD(Cost)   | VARIANCE(Cost)   |
+-----------+-----------+-------------+------------------+
|    581769 |         8 | 105931.1676 | 11221412265.3594 |
+-----------+-----------+-------------+------------------+

We use the SUM() function to get the sum of all values in the Cost column. We count the number of cars in the table with the COUNT() function. Finally, we get the standard deviation and variance using the STD() and VARIANCE() functions.

String functions

In this group we have various strings related functions.

mysql> SELECT LENGTH('ZetCode'), UPPER('ZetCode'), LOWER('ZetCode');
+-------------------+------------------+------------------+
| LENGTH('ZetCode') | UPPER('ZetCode') | LOWER('ZetCode') |
+-------------------+------------------+------------------+
|                 7 | ZETCODE          | zetcode          |
+-------------------+------------------+------------------+

The LENGTH() function returns the length of a string. The UPPER() function converts characters into upper-case letters. The LOWER() function converts characters into lower-case letters.

ysql> SELECT LPAD(RPAD("ZetCode", 10, "*"), 13, "*");
+-----------------------------------------+
| LPAD(RPAD("ZetCode", 10, "*"), 13, "*") |
+-----------------------------------------+
| ***ZetCode***                           |
+-----------------------------------------+

We use the LPAD() and RPAD() functions to append and prepend characters to a specified string. The "ZetCode" string has 7 characters. The RPAD() function appends 3 '*' characters to the string, which will be now 10 characters long.

mysql> SELECT REVERSE('ZetCode'), REPEAT('*', 6);
+--------------------+----------------+
| REVERSE('ZetCode') | REPEAT('*', 6) |
+--------------------+----------------+
| edoCteZ            | ******         |
+--------------------+----------------+

The REVERSE() function reverses the characters in a string. The REPEAT() function repeats a string specified number of times.

mysql> SELECT LEFT('ZetCode', 3), RIGHT('ZetCode', 3), 
    -> SUBSTRING('ZetCode', 3, 3);
+--------------------+---------------------+----------------------------+
| LEFT('ZetCode', 3) | RIGHT('ZetCode', 3) | SUBSTRING('ZetCode', 3, 3) |
+--------------------+---------------------+----------------------------+
| Zet                | ode                 | tCo                        |
+--------------------+---------------------+----------------------------+

The LEFT() function returns 3 leftmost characters, the RIGHT() function returns 3 characters from the right. The SUBSTRING() function returns three characters from the third position of the string.

mysql> SELECT STRCMP('byte', 'byte'), CONCAT('three', ' apples');
+------------------------+----------------------------+
| STRCMP('byte', 'byte') | CONCAT('three', ' apples') |
+------------------------+----------------------------+
|                      0 | three apples               |
+------------------------+----------------------------+

The STRCMP() compares two strings and returns 0 if they are the same. The CONCAT() function concatenates two strings.

mysql> SELECT REPLACE('basketball', 'basket', 'foot');
+-----------------------------------------+
| REPLACE('basketball', 'basket', 'foot') |
+-----------------------------------------+
| football                                |
+-----------------------------------------+

The REPLACE() function returns a string, in which we have replaced some text. The first parameter is the original string. The second parameter is a string, we want to replace. And the last parameter is the new replacing string.

Date & time functions

In this group we have various date and time functions.

mysql> SELECT DAYNAME('2011-01-23'), YEAR('2011/01/23'),
    -> MONTHNAME('110123');
+-----------------------+--------------------+---------------------+
| DAYNAME('2011-01-23') | YEAR('2011/01/23') | MONTHNAME('110123') |
+-----------------------+--------------------+---------------------+
| Sunday                |               2011 | January             |
+-----------------------+--------------------+---------------------+

In MySQL, date is written in the format YYYY-MM-DD. Year is followed by month and day. They can be separated by slash or by hyphen. MySQL also supports a shortened date format, without separators. Time is written in a standard form, HH:MM:SS. Hours followed by minutes and seconds.

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2011-01-22 00:24:49 |
+---------------------+

The NOW() function returns the current date and time.

mysql> SELECT CURTIME(), CURDATE();
+-----------+------------+
| CURTIME() | CURDATE()  |
+-----------+------------+
| 00:25:03  | 2011-01-22 |
+-----------+------------+

The CURTIME() returns the current time and the CURDATE() returns the current date.

mysql> SELECT DATEDIFF('2011-3-12', '2011-1-12');
+------------------------------------+
| DATEDIFF('2011-3-12', '2011-1-12') |
+------------------------------------+
|                                 59 |
+------------------------------------+

With the DATEDIFF() we get the number of days between two dates.

mysql> SELECT DAYNAME('1982-4-12'), MONTHNAME('1982-4-12') ;
+----------------------+------------------------+
| DAYNAME('1982-4-12') | MONTHNAME('1982-4-12') |
+----------------------+------------------------+
| Monday               | April                  |
+----------------------+------------------------+

The DAYNAME() function returns the day name of a date. The MONTHNAME() function returns a month name of a date.

mysql> SELECT WEEKOFYEAR('110123'), WEEKDAY('110123'),
    -> QUARTER('110123');
+----------------------+-------------------+-------------------+
| WEEKOFYEAR('110123') | WEEKDAY('110123') | QUARTER('110123') |
+----------------------+-------------------+-------------------+
|                    3 |                 6 |                 1 |
+----------------------+-------------------+-------------------+

January 23, 2011 can be written in a shortened date format, 110123. We use the WEEKOFYEAR() to find out the week of the year. The WEEKDAY() returns 6, which is Sunday. And the QUARTER() function returns the quarter of the year.

mysql> SELECT DATE_FORMAT('110123', '%d-%m-%Y');
+-----------------------------------+
| DATE_FORMAT('110123', '%d-%m-%Y') |
+-----------------------------------+
| 23-01-2011                        |
+-----------------------------------+

To display date in a different format, we use the DATE_FORMAT().

mysql> SELECT DATE_ADD('110123', INTERVAL 45 DAY), 
    -> SUBDATE('110309', INTERVAL 45 DAY);
+-------------------------------------+------------------------------------+
| DATE_ADD('110123', INTERVAL 45 DAY) | SUBDATE('110309', INTERVAL 45 DAY) |
+-------------------------------------+------------------------------------+
| 2011-03-09                          | 2011-01-23                         |
+-------------------------------------+------------------------------------+

We can use DATE_ADD() to add time intervals to a date and SUBDATE() to subtract time intervals from a date.

System functions

System functions provide some system information about MySQL database.

mysql> SELECT VERSION(), DATABASE();
+--------------------+------------+
| VERSION()          | DATABASE() |
+--------------------+------------+
| 5.1.41-3ubuntu12.8 | mydb       |
+--------------------+------------+

We get the version of the MySQL database and the current database name.

mysql> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+

The USER() function returns the user name and the host name provided by the client.

mysql> SELECT CHARSET('ZetCode'), COLLATION('ZetCode');
+--------------------+----------------------+
| CHARSET('ZetCode') | COLLATION('ZetCode') |
+--------------------+----------------------+
| utf8               | utf8_general_ci      |
+--------------------+----------------------+

The CHARSET() function returns the character set of the argument. The COLLATION() returns the collation of the current string argument. They depend on the charset and collation of the client in use.

Mysql: Display mysql server information

<?php
//enter you own username and password below
mysql_connect("localhost", "", "") or die("Could not connect: " . mysql_error());
//display server version
printf ("MySQL server version: %s\n", mysql_get_server_info());
?>