-1

I have Phone_number column in my table where multiple numbers inserted with character values(eg.(123).254,5674). I need to compare each values without character string and needs to remove duplicate values. Column value= '(245)289.4321,(897)201-7210,(897)2017210,8964253712' I need to remove duplicates and only need distinct values. Thanks In advance for your help

Shivashant
  • 15
  • 5
  • 1
    You are learning firsthand the need for first normal form in data normalization! Also why not to store formatting characters along with the data. If you have the power, fix the database design to eliminate multiple values in one column, which is the root cause of your issues here. – Gary_W Nov 14 '18 at 13:26

1 Answers1

0

This should work:

with tmp_tbl as
(select '(245)289.4321,(897)201-7210,(897)2017210,8964253712' phone_numbers  from dual)
select distinct
  regexp_replace((regexp_substr(tmp_tbl.phone_numbers,'[^,]+', 1, level)),'\D','') phone_number
from 
  tmp_tbl
connect by
  regexp_substr(tmp_tbl.phone_numbers,'[^,]+', 1, level) is not null

Result:

PHONE_NUMBER

8964253712

2452894321

8972017210

Ychdziu
  • 435
  • 5
  • 10
  • Thanks for your help it works, If we need to replace value('(245)289.4321,(897)201-7210,(897)2017210,8964253712') with column name i am getting problem. Can you please suggest on this – Shivashant Nov 14 '18 at 11:34