Test MSSQL casting from (invalid) character to integer/numeric.

test_mssql_char_to_int.R

library(dbplyr)
library(dplyr, warn.conflicts = FALSE)

df <- data.frame(x = c("1.3", "2x")) 
# both a 'float' (non-integer) and an 'invalid' number in the column
con <- DBI::dbConnect(...) # connection to a MSSQL database
db <- copy_to(con, df, "##test")

db %>% mutate(
  float = TRY_CAST(x %as% sql("FLOAT")),
  integer = TRY_CAST(x %as% sql("INTEGER")),
  bigint = TRY_CAST(x %as% sql("BIGINT")),
  numeric = TRY_CAST(x %as% sql("NUMERIC")),
  doublecast_integer = TRY_CAST(TRY_CAST(x %as% sql("NUMERIC")) %as% sql("INTEGER")),
  doublecast_bigint = TRY_CAST(TRY_CAST(x %as% sql("NUMERIC(38,0)")) %as% sql("BIGINT"))
)

R version 3.5.3 on Windows 10. Microsoft SQL Server 12.00.2000 (2014), on Windows 10.

Results of above gist:

# Source:   lazy query [?? x 7]
# Database: Microsoft SQL Server 12.00.2000[bpsrawdata@DESKTOP-BH8LGMO\BPSINSTANCE/bpssamples]
  x     float integer bigint  numeric doublecast_integer doublecast_bigint
  <chr> <dbl>   <int> <int64>   <dbl>              <int> <int64>          
1 1.3     1.3      NA NA            1                  1  1               
2 2x     NA        NA NA           NA                 NA NA