How to treat text with \n and new line as same in vertica
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
0
Best Answers
-
VValdar Vertica Employee Employee
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
0 -
mosheg Vertica Employee Administrator
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
1