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

Best Answers

• Options

  VValdar Vertica Employee Employee

  August 2023 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
  

  0
• Options

  mosheg Vertica Employee Administrator

  August 2023 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
  

  1