MySQL Tutorial/String Functions/MAKE SET

Make first name and last name as a set

   <source lang="sql">

mysql> mysql> CREATE TABLE Employee(

   ->     id            int,
   ->     first_name    VARCHAR(15),
   ->     last_name     VARCHAR(15),
   ->     start_date    DATE,
   ->     end_date      DATE,
   ->     salary        FLOAT(8,2),
   ->     city          VARCHAR(10),
   ->     description   VARCHAR(15)
   -> );

Query OK, 0 rows affected (0.03 sec) mysql> mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->              values (1,"Jason",    "Martin",  "19960725",  "20060725", 1234.56, "Toronto",  "Programmer");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(2,"Alison",   "Mathews",  "19760321", "19860221", 6661.78, "Vancouver","Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(3,"James",    "Smith",    "19781212", "19900315", 6544.78, "Vancouver","Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(4,"Celia",    "Rice",     "19821024", "19990421", 2344.78, "Vancouver","Manager");

Query OK, 1 row affected (0.02 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(5,"Robert",   "Black",    "19840115", "19980808", 2334.78, "Vancouver","Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(6,"Linda",    "Green",    "19870730", "19960104", 4322.78,"New York",  "Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(7,"David",    "Larry",    "19901231", "19980212", 7897.78,"New York",  "Manager");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(8,"James",    "Cat",     "19960917",  "20020415", 1232.78,"Vancouver", "Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> select * from Employee; +------+------------+-----------+------------+------------+---------+-----------+-------------+ | id | first_name | last_name | start_date | end_date | salary | city | description | +------+------------+-----------+------------+------------+---------+-----------+-------------+ | 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer | | 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester | | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester | | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager | | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester | | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester | | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager | | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester | +------+------------+-----------+------------+------------+---------+-----------+-------------+ 8 rows in set (0.00 sec) mysql> mysql> mysql> mysql> mysql> select make_set(1,first_name, last_name) from EMployee; +-----------------------------------+ | make_set(1,first_name, last_name) | +-----------------------------------+ | Jason | | Alison | | James | | Celia | | Robert | | Linda | | David | | James | +-----------------------------------+ 8 rows in set (0.00 sec) mysql> mysql> mysql> mysql> drop table Employee; Query OK, 0 rows affected (0.00 sec) mysql></source>

MAKE_SET(0,"a","b","c");

   <source lang="sql">

mysql> mysql> mysql> SELECT MAKE_SET(0,"a","b","c"); +-------------------------+ | MAKE_SET(0,"a","b","c") | +-------------------------+ | | +-------------------------+ 1 row in set (0.02 sec)</source>

MAKE_SET(1 | 4,"A","B","C");

   <source lang="sql">

mysql> mysql> mysql> SELECT MAKE_SET(1 | 4,"A","B","C"); +-----------------------------+ | MAKE_SET(1 | 4,"A","B","C") | +-----------------------------+ | A,C | +-----------------------------+ 1 row in set (0.02 sec)</source>

MAKE_SET(1 | 4,"A","B",NULL,"C")

   <source lang="sql">

mysql> mysql> SELECT MAKE_SET(1 | 4,"A","B",NULL,"C"); +----------------------------------+ | MAKE_SET(1 | 4,"A","B",NULL,"C") | +----------------------------------+ | A | +----------------------------------+ 1 row in set (0.00 sec)</source>

MAKE_SET(bits,str1,str2,...): Returns a string containing substrings separated by "," characters

str1 corresponds to bit 0, str2 to bit 1, and so on.

NULL values in str1, str2, ... are not appended to the result.

   <source lang="sql">

mysql> mysql> SELECT MAKE_SET(1,"a","b","c"); +-------------------------+ | MAKE_SET(1,"a","b","c") | +-------------------------+ | a | +-------------------------+ 1 row in set (0.00 sec) mysql></source>