REGEXP_REPLACE in ETL add new line?

mberkeley

I have data in Domo for an address that includes a new line character. This data was brought in from NetSuite in this format. I am trying to add additional data that matches this format.

I have the following data: Ship Address: attn~company~addr1~addr2~city, state zip

I would like it saved as such:

attn

company

addr1

addr2

city, state zip

Here is the regex I'm trying:

REGEXP_REPLACE('Ship Address','~',' \\n')

The results look like: 'attn \ncompany...'

I've tried \n, \\n, \r, \\r

I know the data can be stored with a new line because the NetSuite data is already like that.

Here is the tested regex:

Any ideas?

GrantSmith

\r and/or \n should work but it appears they don't. You can explicitly pass an enter character in your forumla tile for the time being:

REGEXP_REPLACE(`address`, '~', '
')

mberkeley

I also noticed that the REGEXP_REPLACE() function isn't allowing the 'g' flag, saying that there is an error that REGEXP_LIKE doesn't recognize that flag.

GrantSmith

\r and/or \n should work but it appears they don't. You can explicitly pass an enter character in your forumla tile for the time being:

REGEXP_REPLACE(`address`, '~', '
')

mberkeley

I can't believe that works, but it does! thanks.