mysql - 搜索 UPPER 中前两个字符的字段

Question

我正在尝试查询一个值为 ONeil 的 SQL Server 表。我的目标是在查询的数据中添加撇号。我知道从源头(在 PHP 中)解决问题会更容易，但无法访问这些脚本

表结构，

ID      First_Name     Last_Name
1       John           ONEil
2       Sam            OMally
3       Sarah          OConner

Answer 1

表格

mysql> select * from names where LEFT(last_name, 2) = UPPER(LEFT(last_name, 2));
+-----------+
| last_name |
+-----------+
| Michael   |
| Blah      |
| ONeil     |
+-----------+

更新查询

update names
set last_name = concat(left(last_name, 1), '\'', right(last_name, length(last_name)-1))
where binary left(last_name, 2) = upper(left(last_name, 2))
  and binary upper(left(last_name, 3)) <> left(last_name, 3);

结果

+-----------+
| last_name |
+-----------+
| Michael   |
| Blah      |
| O'Neil    |
+-----------+