我正在尝试使用REGEXP_REPLACE与众不同,并返回0行.
我已经在MySQLP v8.0中创建了一个测试表
CREATE TABLE phone(
id serial primary key,phone_number char(25));
INSERT INTO phone (phone_number)
VALUES ('(423) 330-9999');
INSERT INTO phone (phone_number)
VALUES ('(423)3309999');
INSERT INTO phone (phone_number)
VALUES ('423-330-1111)');
INSERT INTO phone (phone_number)
VALUES ('1-423-330-6666');
INSERT INTO phone (phone_number)
VALUES ('1A423*330*1111');
INSERT INTO phone (phone_number)
VALUES ('5553301111');
– 然后
select
REGEXP_REPLACE(phone_number,'[^0-9]','',1,'m') as clean_phone
from phone
—工作正常->
clean_phone
4233309999
4233309999
4233301111
14233306666
14233301111
5553301111
—计数
select
count(REGEXP_REPLACE(phone_number,'m')) as
clean_phone
from phone
—工作正常->
clean_phone
6
-独特的clean_phone
select
distinct(REGEXP_REPLACE(phone_number,'m')) as
clean_phone
from phone
—返回空->
clean_phone
我不明白为什么区分不起作用？
最佳答案
Distinct不是函数,因此您不需要distinct(),而仅需要distinct
select distinct REGEXP_REPLACE(phone_number,'m') as
clean_phone
from phone
.
select distinct clear_phone from(
select REGEXP_REPLACE(phone_number,'m') clear_phone
from phone ) t
如果错误仍然存在,您可以尝试使用虚拟表的插入/选择
insert into dummy_table(clear_phone)
select REGEXP_REPLACE(phone_number,'m')
from phone;
select distinct clear_phone from dummy_table;