CODEWITHSUNDEEP
rsql-serverdbplyr
moabit21
moabit21

Reputation: 659

Using regular expressions with dbplyr on a SQL Server database

I was thrilled to see that dbplyr supports the str_detect() command. However, it doesn't work properly with regular expressions when I do a query on a SQL Server database via a ODBC connection: the special character "." is, for instance, not interpreted as a wildcard, but as the character that it is, so a period. Are there any workarounds?

For example,

my_tbl%>%filter(str_detect(COL1, "A123.4"))

will match "A123.4", but not "A123x4".

Upvotes: 2

Views: 512

Answers (1)

Xedni
Xedni

Reputation: 4695

Sql server does not support regular expressions. The only ways to do so would be to write a custom CLR assembly for a proc/function, or do what you can with wildcards before applying a regex in your application code.

Fwiw you can achieve your goal with 'A123_4' where the underscore is the closest equivalent to "." In regex

Upvotes: 1

Related Questions