Translate str_starts() to REGEXP_INSTR instead of INSTR for Snowflake

[nathanhaigh]

Snowflake doesn't have a INSTR function. As such, I think the following stringr functions are affected:

• str_detect() when fixed() is used
• str_ends()
• str_starts()

x <- c("XXX", "XXY", "YXXX")

#####
# str_detect()
#####
lazy_frame(TOKEN=x,con = simulate_snowflake()) %>%
  filter(
    stringr::str_detect(TOKEN, fixed("XXX"))
  )
<SQL>
SELECT `df`.*
FROM `df`
WHERE (INSTR(`TOKEN`, 'XXX') > 0)

#####
# str_starts()
#####
lazy_frame(TOKEN=x,con = simulate_snowflake()) %>%
  filter(
    stringr::str_starts(TOKEN, fixed("XXX"))
  )
<SQL>
SELECT `df`.*
FROM `df`
WHERE (INSTR(`TOKEN`, 'XXX') = 1)

#####
# str_ends()
#####
lazy_frame(TOKEN=x,con = simulate_snowflake()) %>%
  filter(
    stringr::str_ends(TOKEN, fixed("XXX"))
  )
<SQL>
SELECT `df`.*
FROM `df`
WHERE (INSTR(`TOKEN`, 'XXX') = ((LENGTH(`TOKEN`) - LENGTH('XXX')) + 1))

[nathanhaigh]

I see from R/backend-snowflake.R the str_detect() function is hacking around the fact that REGEXP anchors the start and end by default. A simpler solution is to use CONTAINS() instead.

[hadley]

Fixed in #1406