MySQL查询以显示单独列中字符串的第一个字母
要从字符串中获取第一个字母,请使用LEFT()
。此方法使您可以从字符串的左侧返回字符。
我们首先来看一个示例并创建一个表-
mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, FirstName varchar(100) );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable(FirstName) values('John'); mysql> insert into DemoTable(FirstName) values('Adam'); mysql> insert into DemoTable(FirstName) values('Jace'); mysql> insert into DemoTable(FirstName) values('Chris'); mysql> insert into DemoTable(FirstName) values('Bob'); mysql> insert into DemoTable(FirstName) values('Carol');
使用select语句显示表中的所有记录-
mysql> select *from DemoTable;
这将产生以下输出-
+----+-----------+ | Id | FirstName | +----+-----------+ | 1 | John | | 2 | Adam | | 3 | Jace | | 4 | Chris | | 5 | Bob | | 6 | Carol | +----+-----------+ 6 rows in set (0.00 sec)
以下是查询以显示带有字符串值的列中的第一个字母。结果显示在新列中-
mysql> select Id,FirstName,upper(left(FirstName,1)) AS FirstLetter from DemoTable order by FirstName;
这将产生以下输出-
+----+-----------+-------------+ | Id | FirstName | FirstLetter | +----+-----------+-------------+ | 2 | Adam | A | | 5 | Bob | B | | 6 | Carol | C | | 4 | Chris | C | | 3 | Jace | J | | 1 | John | J | +----+-----------+-------------+ 6 rows in set (0.03 sec)