CODEWITHSUNDEEP
google-bigquery
Lijju Mathew
Lijju Mathew

Reputation: 2091

Remove embeded newline character in a big query table column using a function

I have a big query table with a column that has embedded new line character. I want to remove it using a function. It has to be a done using function and I cannot use properties like allowQuotedNewlines etc. I tried below and it is not working. Appreciate your help here.

select replace(column,'\\n','') from table

Upvotes: 4

Views: 14846

Answers (2)

pmo511
pmo511

Reputation: 619

Just use one slash to escape, try this in the query window:

select '\r', '\n'

Upvotes: 2

Graham Polley
Graham Polley

Reputation: 14791

Without seeing your SQL or the error, it's hard to know what you're doing wrong. But REPLACE works just fine for me:

SELECT
  new_line_delims,
  REPLACE(new_line_delims, '\\n', '') AS new_line_delims_replaced
FROM (
  SELECT
    '\\nThis has several embedded\\n new line delims\\n' AS new_line_delims)

Upvotes: 8

Related Questions