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

vinutauro

I have 2 texts in 2 different rows. One has \n and the other one has a new line entered. While applying distinct I want to treat them as one row. Could someone help

text 1 :
details \r\nwhich has

Text 2:
details
which has

VValdar

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

mosheg

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