REGEXP_REPLACE in ETL add new line?

mberkeley
mberkeley Member
edited July 2022 in Magic ETL

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?

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    \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`, '~', '
    ')
    
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • 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
    GrantSmith Coach
    Answer ✓

    \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`, '~', '
    ')
    
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • I can't believe that works, but it does! thanks.