Unrecognized escape in character saved in a string for regex

General
, ,
1

Hi all,

recently I encountered a problem about "pull" .sql code into R and save as str link

Now that I solved it as mentioned in the reply, I found another problem about unrecognized escape regex character.

So in the single string that saves my .sql file, I have something like this, which I would used in IDE like datagrip with no problem. The first 'replace' is to remove 3 consecutive period characters , and the second replace is to remove all the double quotes.

SELECT
                        key_id,
                        REPLACE(REPLACE(system_type, '\.\.\.', ''), '"', '') system_type
                        
                        FROM database.table

However, when I use such clause in R by saving them in a string, it would require double escape just because they are string, or there would be error message like '\.' is an unrecognized escape in character string.

I do not want to change anything in .sql file, because it is used across multiple software in our team, such as Tableau, which does not require double escape for regex ... (or .{3}), so is there a way that I can use the same sql in both R and Tableau?

2

Hi, how about creating a custom function that wraps the DBI function and modifies the string if needed? That way you're just modifying a variable that contains the SQL query, and not the .sql file.