Below is how my data appears in one of the tables in my database

How can I achieve below in my select statement.

Have tried to use regex but in vain. A sample select query will be highly appreciated.

CodePudding user response：

You can use a self-join:

select distinct case when t1.column1 < t2.column1 then t1.column1 else t2.column1 end, 
    case when t2.column1 > t1.column1 then t2.column1 else t1.column1 end from t t1 
join t t2 
on regexp_replace(t1.column1, '\W', '', 'g') = regexp_replace(t2.column1, '\W', '', 'g') and t1.id != t2.id

CodePudding user response：

Providing you are looking to replace just the characters '-' and ' ', you can use the replace function twice. Use a simple regular expression to identify the rows to process:

with test(id, column1) as 
     (values (1, '1234-6 1')
           , (2, '123461')
           , (3, '023-fd-0')
           , (4, '023fd0')
           , (5, '5fd23fo')
           , (6, '5fd 23f o')
           , (7, '12334')
      )
select id
     , replace(replace(column1,'-',''),' ','') as column1
     , column1 as column2
  from test 
 where column1 ~ '(-|\ )';