Any chance support for str_replace_all with MSSQL will be incorporated in dbplyr?
Hey @rgouvea ! That's a great question!! I worked on
str_replace_* stuff in
dbplyr at one point, and I suspect there was a reason that I did not add this. Do you know how to do that type of string replacing in native MSSQL? i.e. in a pure SQL query?
dbplyr translates to SQL, so we are dependent on the database provider having the appropriate functionality in order for us to translate as such.
I'm not an advanced user of MSSQL, so it would be great if other advanced users could pitch in. Doing some research to figure out why
str_replace_* would not work or how to implement what I needed using a pure SQL query, I discovered that the function
REPLACE ( string_expression , string_pattern , string_replacement ) (see link to documentation below) will do a replace all. The main problem is that it does not accept REGEX, limiting too much the functionality. As far as I understood, it seems there is no native way (without using CLR) to use REGEX expressions in MSSQL. But, I may be wrong, since as I said, I have just recently starting using MSSQL.
Link to documentation: https://docs.microsoft.com/en-us/sql/t-sql/functions/replace-transact-sql?view=sql-server-ver15
I believe you are right here. Good find! I did a cursory search back through adding the
stringr functionality and found where we excluded MSSQL. I believe it was because functionality would be so different from
str_replace_all() as to almost be unintelligible, so we opted for a warning / error.
If you have any avenue to request features from MSSQL, please do so and ask for regexp replace!
As you can see, in MySQL and Postgres, we use
regexp_replace to provide this translation:
This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.