Help with Replace function

[ekta22]

Hi All,

I am using the select statement below to get rid of the duplicates. The issue I am having is that 3 of the fields that I am grouping on are description fields. In some cases the records may be duplicate but because the user entered some extra punctuation like /,.() etc. it would not be picked up. I wanted to get rid of all the punctuation in these three fields so a record is not considered unique because of some extra punctuation.

How can I do that?

SELECT * FROM TABLE1
WHERE TABLE1.ROWID IN
(
SELECT MIN(ROWID)
FROM TABLE1
GROUP BY
TRIM(INTERVAL), TRIM(DESC), TRIM(TEXT_LINE1) || TRIM(TEXT_LINE2));

I want to use the REPLACE function on DESC, TEXT_LINE1 and TEXT_LINE2.

Thanks,
-E

 

[engineer2100]

Hope this helps

SELECT * FROM TABLE1  
WHERE TABLE1.ROWID IN
(
  SELECT MIN(ROWID)
  FROM TABLE1
  GROUP BY
  trim(translate(INTERVAL,',.',' ')),
  trim(translate(DESC,',.',' ')),
  trim(translate(TEXT_LINE1,',.',' ') ||
    trim(translate(TEXT_LINE2,',.',' ')
);

 

[Dagon]

You might also want to consider applying a function like UPPER or LOWER to ensure a consistent letter case.