How to treat text with \n and new line as same in vertica

It may be depending on the OS your using, but on my Windows DBeaver client this works:

with cte_data (str) as
(
select 'details\r\nwhich has'::varchar(30)
 union all
select 'details
which has'::varchar(30)
)
select distinct replace(replace(str, '\r', chr(13)), '\n', chr(10)) as str
  from cte_data;

str
-----------------
details¶which has

You can also do the opposite replacement:

select distinct replace(replace(str, chr(13), '\r'), chr(10), '\n') as str
  from cte_data;

str
--------------------
details\r\nwhich has

In addition, consider the following example to reduce the count distinct elapsed time.

CREATE TABLE public.my_generated_data
( row_id int, f1 varchar(100), f2 varchar(100) default REGEXP_REPLACE(f1,'\s|\\r|\\n') );

[..] INSERT 200 million rows, some with \r\n and some with new line.

SELECT count(distinct f2) FROM my_generated_data;
 count
-------
     1
(1 row)

Time: First fetch (1 row): 702.522 ms. All rows formatted: 702.548 ms

CREATE PROJECTION my_lap4count AS SELECT f2 as f2, MAX(f2) AS myc FROM my_generated_data GROUP BY 1;
SELECT REFRESH('my_generated_data');

SELECT count(f2) FROM my_lap4count;    -- No need for distinct because of the max in the LAP
 count
-------
     1
(1 row)

Time: First fetch (1 row): 13.202 ms. All rows formatted: 13.244 ms