snowflake-cloud-data-platform

I am trying to edit the "\n" new line character from a column of strings, how can I modify the data in the already loaded table?

I have been trying to remove all newlines from a table column and replace with spaces. I have tried the following:

And none of them work.

None of these queries are throwing an error, but they also aren't removing any of the newlines.

Just to clarify, for most of these queries, if I run them on an individual string, they work. For example, select regexp_replace('hello \n world', '\n',' ') gives the desired output of "hello world". However, running it on an entire column doesn't seem to work. (Running other replace queries have worked on the entire column - it's just this newline one that seems to not work.)

Here is some data that I generated that we can test out, this is my .sql file, before the answers were for a new line character not the literal "\n" character which is what I want.

What I tried: 

USE Warehouse "Test";
Create database "strings";
Use database "strings";

CREATE OR REPLACE TABLE Lora_ipsum(line varchar); 

INSERT INTO Lora_ipsum Values
('What is Lorem Ipsum?\n'),
('Lorem Ipsum is simply dummy text of the printing and typesetting industry.\n'),
( 'Lorem Ipsum has been the industrys standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book.\n'),
('It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged.\n'),
('It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.\n'),
('Why do we use it?\n'),
('It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout.\n'),
( 'The point of using Lorem Ipsum is that it has a more-or-less normal distribution of letters, as opposed to using Content here, content here,\n'),
(' making it look like readable English. Many desktop publishing packages and web page editors now use Lorem Ipsum as their default model text,\n'),
('and a search for lorem ipsum will uncover many web sites still in their infancy.\n'),
('Various versions have evolved over the years, sometimes by accident, sometimes on purpose (injected humour and the like).\n'),
('\n'),
('Where does it come from?\n'),
('Contrary to popular belief\n'); 

Select line FROM Lora_ipsum;

Lora_ipsum table with varchar, data is blue

The result was all of the data points were stored in varchar and the \n were not visible, I used single quotes.

So I tried the suggested solutions where: 

SELECT REPLACE(line,'\n',' ') FROM Lora_ipsum;

Where the result changed a few of the data varchar items to blue and black: enter image description here

The second suggestion I used had no space in the 3rd item as: 

select replace(line,'\n','') from Lora_ipsum;

The results were the same.

If I had a literal "\n" in a string, is the datatype of my table incorrect, is there a regex to remove the literal instead of the new line? Thank you for your help.

Solution

  • This is strange, all of the following work for me (I use # instead of   so I can see):

    SELECT
  LINE,
  TRANSLATE(LINE, '\r\n','##') "TRANSLATE",
  REGEXP_REPLACE(LINE, '\r?\n','#') "REGEXP_REPLACE",
  REPLACE(LINE, '\n','#') "REPLACE",
  REPLACE(LINE, CHR(10),'#') "REPLACE_CHR"
FROM LORA_IPSUM;

    Maybe this was a bug that has been fixed? @Rachel: How old is this case?