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

modified version of Gist as originally written by Hadley Wickham. For testing purposes of dbplyr in the tidyverse (tidyverse/dbplyr#555)

Can you please include the results too?

R version 4.0.3 on openSuSE Linux. Microsoft SQL Server version 15.00.4043 (2019), on openSuSE Linux.

Results of above gist :

# Source:   lazy query [?? x 7]
# Database: Microsoft SQL Server 15.00.4043[dbo@localhost/master]
  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

TRY_CAST to INTEGER or BIGINT returns NULL/NA for entire column if invalid number (in this case, with an 'x') in a single row and a non-integer value in another row. Normally, the non-integer value can be successfully cast to integer.

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