SQL/MySQL/String/String Append

Материал из SQL эксперт
Версия от 13:16, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Append string in select clause

   <source lang="sql">

/* mysql> select * from employee; +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ | id | firstname | lastname | title | age | yearofservice| salary | perks | email | +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ | 1 | John | Chen | Senior Programmer | 31 | 3| 120000 | 25000 | j@hotmail.ru | | 2 | Jan | Pillai | Senior Programmer | 32 | 4| 110000 | 20000 | g@yahoo.ru | | 3 | Ane | Pandit | Web Designer | 24 | 3| 90000 | 15000 | a@gmail.ru | | 4 | Mary | Anchor | Web Designer | 27 | 2| 85000 | 15000 | m@mail.ru | | 5 | Fred | King | Programmer | 32 | 3| 75000 | 15000 | f@net.ru | | 6 | John | Mac | Programmer | 32 | 4| 80000 | 16000 | j@hotmail.ru | | 7 | Arthur | Sam | Programmer | 28 | 2| 75000 | 14000 | e@yahoo.ru | | 8 | Alok | Nanda | Programmer | 32 | 3| 70000 | 10000 | a@yahoo.ru | | 9 | Susan | Ra | Multimedia Programmer | 32 | 4| 90000 | 15000 | h@gmail.ru | | 10 | Paul | Simon | Multimedia Programmer | 23 | 1| 85000 | 12000 | ps@gmail.ru | | 11 | Edward | Parhar | Multimedia Programmer | 30 | 2| 75000 | 15000 | a@hotmail.ru | | 12 | Kim | Hunter | Senior Web Designer | 32 | 4| 110000 | 20000 | kim@coolmail.ru | | 13 | Roger | Lewis | System Administrator | 32 | 3| 100000 | 13000 | roger@mail.ru | | 14 | Danny | Gibson | System Administrator | 31 | 2| 90000 | 12000 | danny@hotmail.ru | | 15 | Mike | Harper | Senior Marketing Executive | 36 | 1| 120000 | 28000 | m@gmail.ru | | 16 | Mary | Sunday | Marketing Executive | 31 | 5| 90000 | 25000 | monica@bigmail.ru | | 17 | Jack | Sim | Marketing Executive | 27 | 1| 70000 | 18000 | hal@gmail.ru | | 18 | Joe | Irvine | Marketing Executive | 27 | 1| 72000 | 18000 | joseph@hotmail.ru | | 19 | Henry | Ali | Customer Service Manager | 32 | 3| 70000 | 9000 | shahida@hotmail.ru | | 20 | Peter | Champion | Finance Manager | 32 | 2| 120000 | 25000 | peter@yahoo.ru | +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ 20 rows in set (0.00 sec) mysql> SELECT CONCAT(firstname, " ", lastName)

   -> from employee
   -> where title = "Programmer";

+----------------------------------+ | CONCAT(firstname, " ", lastName) | +----------------------------------+ | Fred King | | John Mac | | Arthur Sam | | Alok Nanda | +----------------------------------+ 4 rows in set (0.00 sec)

  • /

Drop table employee; CREATE TABLE employee (

   id int unsigned not null auto_increment primary key,
   firstname varchar(20),
   lastname varchar(20),
   title varchar(30),
   age int,
   yearofservice int,
   salary int,
   perks int,
   email varchar(60)

);

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");

select * from employee;

SELECT CONCAT(firstname, " ", lastName) from employee where title = "Programmer";


      </source>
   
  


Concatenate string with NULL value

   <source lang="sql">

/* mysql> select CONCAT("a",null); +------------------+ | CONCAT("a",null) | +------------------+ | NULL | +------------------+ 1 row in set (0.00 sec)

  • /

select CONCAT("a",null);

      </source>
   
  


Concatenating String Values: Joining Strings

   <source lang="sql">

/* mysql> SELECT ClassID, CONCAT(Time, ", Room ", RoomID) AS ClassDetails

   -> FROM Class;

+---------+------------------------------------------+ | ClassID | ClassDetails | +---------+------------------------------------------+ | 1 | Mon 09:30-11:00, Room 6 | | 2 | Mon 11:30-12:00, Thu 09:00-11:00, Room 5 | | 3 | Mon 14:30-16:00, Room 3 | +---------+------------------------------------------+ 3 rows in set (0.00 sec)

  • /

/* Prepare the data */ Drop TABLE Class; CREATE TABLE Class (

  CourseID    INT NOT NULL,
  RoomID      INT NOT NULL,
  ClassID     INT NOT NULL PRIMARY KEY,
  Time        VARCHAR(50)

)TYPE = InnoDB; /* Insert data for testing */ INSERT INTO Class (ClassID,CourseID,RoomID,Time) VALUES (1,1,6,"Mon 09:30-11:00"); INSERT INTO Class (ClassID,CourseID,RoomID,Time) VALUES (2,2,5,"Mon 11:30-12:00, Thu 09:00-11:00"); INSERT INTO Class (ClassID,CourseID,RoomID,Time) VALUES (3,3,3,"Mon 14:30-16:00"); /* Real command */

SELECT ClassID, CONCAT(Time, ", Room ", RoomID) AS ClassDetails FROM Class;

      </source>
   
  


Simplest concatenate string 2

   <source lang="sql">

/* mysql> select CONCAT("a","B"); +-----------------+ | CONCAT("a","B") | +-----------------+ | aB | +-----------------+ 1 row in set (0.00 sec)

  • /

select CONCAT("a","B");

      </source>
   
  


Simplest concatenate string: space

   <source lang="sql">

/* mysql> select CONCAT("a"," ","B"); +---------------------+ | CONCAT("a"," ","B") | +---------------------+ | a B | +---------------------+ 1 row in set (0.00 sec)

  • /

select CONCAT("a"," ","B");

      </source>
   
  


String function: concat

   <source lang="sql">

/* mysql> Drop table Employees; ERROR 1051 (42S02): Unknown table "employees" mysql> CREATE TABLE Employees

   ->    (
   ->    EmpID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   ->    EmpFN VARCHAR(20) NOT NULL,
   ->    EmpMN VARCHAR(20) NULL,
   ->    EmpLN VARCHAR(20) NOT NULL
   ->    )
   -> ENGINE=INNODB;

Query OK, 0 rows affected (0.13 sec) mysql> INSERT INTO Employees (EmpFN, EmpMN, EmpLN)

   -> VALUES ("John", "P.", "Smith"),
   ->        ("Robert", NULL, "Schroader"),
   ->        ("Mary", "Marie", "Michaels"),
   ->        ("John", NULL, "Laguci"),
   ->        ("Rita", "C.", "Carter"),
   ->        ("George", NULL, "Brooks");

Query OK, 6 rows affected (0.04 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> SELECT EmpID, UPPER(CONCAT_WS(" ", EmpFN, EmpMN, EmpLN)) AS Name,

   ->    CONCAT(LOWER(LEFT(EmpFN, 2)), LOWER(LEFT(EmpLN, 3)), EmpID) AS RegID
   -> FROM Employees
   -> ORDER BY EmpID;

+-------+---------------------+--------+ | EmpID | Name | RegID | +-------+---------------------+--------+ | 1 | JOHN P. SMITH | josmi1 | | 2 | ROBERT SCHROADER | rosch2 | | 3 | MARY MARIE MICHAELS | mamic3 | | 4 | JOHN LAGUCI | jolag4 | | 5 | RITA C. CARTER | ricar5 | | 6 | GEORGE BROOKS | gebro6 | +-------+---------------------+--------+ 6 rows in set (0.02 sec) mysql>

  • /

Drop table Employees; CREATE TABLE Employees

  (
  EmpID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  EmpFN VARCHAR(20) NOT NULL,
  EmpMN VARCHAR(20) NULL,
  EmpLN VARCHAR(20) NOT NULL
  )

ENGINE=INNODB; INSERT INTO Employees (EmpFN, EmpMN, EmpLN) VALUES ("John", "P.", "Smith"),

      ("Robert", NULL, "Schroader"),
      ("Mary", "Marie", "Michaels"),
      ("John", NULL, "Laguci"),
      ("Rita", "C.", "Carter"),
      ("George", NULL, "Brooks");

SELECT EmpID, UPPER(CONCAT_WS(" ", EmpFN, EmpMN, EmpLN)) AS Name,

  CONCAT(LOWER(LEFT(EmpFN, 2)), LOWER(LEFT(EmpLN, 3)), EmpID) AS RegID

FROM Employees ORDER BY EmpID;

      </source>