Using regular expressions with dbplyr on a SQL Server database

Question

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".

I think you might have a typo in your code. Your regex string is "A123.4" so it wont match "A123x5" because it ends in a 5. It will, however, match "A123x4" and "A123.4". If you need it to also match 5, you could try: str_detect(COL1, "A123.[4-5]") — AndS.
– AndS., Commented Jul 28, 2018 at 4:52
You are right, I had a typo in my post. Thank you for pointing that out. I just corrected it. But the problem remains the same. — moabit21
– moabit21, Commented Jul 28, 2018 at 8:46

Xedni · Accepted Answer · 2018-07-28 08:57:16Z

1

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

answered Jul 28, 2018 at 8:57

Xedni

4,7913 gold badges22 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using regular expressions with dbplyr on a SQL Server database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related