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

Best Answers

  • VValdarVValdar Vertica Employee Employee
    Answer ✓

    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
    
  • moshegmosheg Vertica Employee Administrator
    Answer ✓

    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
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file