MySQL:使用子字符串从两个不同的列中导出数据

Question

我有下表:

CREATE TABLE `vendor_contacts` (
  `vendor_id` int(11) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `name_initials` varchar(45) NOT NULL,
  PRIMARY KEY (`vendor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

使用以下插入语句:

INSERT INTO `vendor_contacts`
    VALUES (5,'Davison','Michelle',''),
           (12,'Mayteh','Kendall',''),
           (17,'Onandonga','Bruce',''),
           (44,'Antavius','Anthony',''),
           (76,'Bradlee','Danny',''),
           (94,'Suscipe','Reynaldo',''),
           (101,'O\'Sullivan','Geraldine',''),
           (123,'Bucket','Charles','');

我想运行一个查询，从名字和姓氏列中提取第一个字母。

SELECT vendor_id, last_name, first_name,  substring(first_name, 1, 1) AS initials
FROM vendor_contacts;

以下指南http://www.w3resource.com/mysql/string-functions/mysql-substring-function.php , 仅显示如何使用一列。

Answer 1

您分别拉取它们并使用 concat() 组合它们:

SELECT vendor_id, last_name, first_name,
       CONCAT(LEFT(first_name, 1), LEFT(last_name, 1)) as initials
FROM vendor_contacts;