mysql-行使DISTINCT的REGEXP_REPLACE

我正在实行行使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

我不大白为什么区分不起浸染？

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;

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!