To add leading zeros to a string in R I'll use the formatC() function:
x <- c(1, 10, 100, 10000, 100000, 1000000)
formatC(x, width = 8, format = "d", flag = "0")
#> [1] "00000001" "00000010" "00000100" "00010000" "00100000" "01000000"
When I try and apply this to a Teradata SQL database I get the following error (guessing that formatC isn't supported):
tbl(db_name, "tbl_name") %>%
mutate(col5 = formatC(col4, width = 9, format = "d", flag = "0"))
#> Error in new_result(connection@ptr, statement) :
#> nanodbc/nanodbc.cpp:7000: 60100: [Teradata][ODBC Teradata Driver]
#> [Teradata Database](-2275)Syntax error: Data Type "col5" does not match a
#> Defined Type name. In addition: Warning message:
#> Named arguments ignored for SQL formatC
How can I add leading zeros to a Teradata SQL database when R is my front end?
In Teradata SQL there's no function named formatC, but you can get a similar reuslt using
to_char(x, '09999999') -- numeric x
LPAD(x, 8, '0') -- varchar x