MySQL查询以LIKE选择行并创建包含匹配字符串的新列？
为此，请使用SUBSTRING()。让我们首先创建一个表-
mysql> create table DemoTable1872 ( Name varchar(20) );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable1872 values('John Doe');
mysql> insert into DemoTable1872 values('Adam Smith');
mysql> insert into DemoTable1872 values('Mitchell Johnson');使用select语句显示表中的所有记录-
mysql> select * from DemoTable1872;
这将产生以下输出-
+------------------+ | Name | +------------------+ | John Doe | | Adam Smith | | Mitchell Johnson | +------------------+ 3 rows in set (0.00 sec)
以下是使用LIKE选择行并创建包含匹配字符串的新列的查询-
mysql> select Name,
substring(Name, locate('John', Name), length('John')) as NewName
from DemoTable1872
where Name like '%John%';这将产生以下输出-
+------------------+---------+ | Name | NewName | +------------------+---------+ | John Doe | John | | Mitchell Johnson | John | +------------------+---------+ 2 rows in set (0.00 sec)