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?
Ultimately dbplyr translates to SQL, so we are dependent on the database provider having the appropriate functionality in order for us to translate as such.
2 Likes
Hi @cole,
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.