Running raw SQL by using Ecto.Migration#execute/1 and cannot match newline pattern

Questions / Help
,
1

Hey all! I’m wanting to create a migration to update every row by replacing a substring if it exists within the row.

I’m using raw SQL since I had issues w/ my implementations pointing towards accessing the schema.

My SQL query essentially wants to find every \n and replace it with <br>. If I run it as a SQL query through PSQL, it works just perfectly and my rows are updated. When I wrap it around execute/1 in a migration it fails to update rows. I’m wondering what the difference is between the same query being run in the migration to have it not identify \n properly.

Snippet from PSQL which works

    UPDATE table
    SET body = regexp_replace(body, '\\n', '<br>', 'g')
    WHERE body IS NOT NULL;

Snippet from migration which doesn’t work.

    execute """
    UPDATE table
    SET body = regexp_replace(body, '\\n', '<br>', 'w')
    WHERE body IS NOT NULL;
    """

The case I’d like is much more specific and at one point want to shove in this pattern: (\r\n|\r|\n), but I can’t get the simple replacement earlier displayed ^^ to work.

Sidenote: I tested to see if it was PSQL/Postgrex versioning not calling execute/1 properly. The function works and I’m able to replace very simple patterns such as replacing all a's with b's via Migration.

2

I don’t know what is equivalent on MSSQL, but you can do same with REPLACE function

UPDATE table
SET body = REPLACE(REPLACE(REPLACE(body,‘\n’,‘X’),‘
’,‘W’),‘g’,‘Z’)
WHERE body IS NOT NULL;

I hope, that help you

3

Thanks :slight_smile: Yup, I’ve tried replace and it works just fine. A colleague wanted to specifically use regexp_replace due to less flexibility, but if that doesn’t work I can fall back to a more strict replace!

4

\ still works inside a plain """ string, so this produces a query that has one \ in it instead of the intended two.

Using the ~S sigil (instead of the implied ~s) turns off interpolation and escaping; the @doc example here seems particularly relevant to your situation.

3 Likes
5

This is great! I’m gonna try using sigil soon, will report back on how it goes! :slight_smile: Thanks so much

6

Update: ~S worked perfectly and the docs you referred to were very useful. Thanks so much again!