R script showing how to convert a string field to an integer and then use it in the WHERE clause of a SQL query.

ckan-sql-example.R

if(!require(ckanr)){           # If the CKAN wrapper isn't already installed,
  install.packages("ckanr")    # install it.
}

library(ckanr)

ckanr_setup(url = "https://data.wprdc.org/") # Set data.wprdc.org as the default URL.

query_8_to_9 = paste0('SELECT * FROM "47350364-44a8-4d15-b6e0-5f79ddff9367" WHERE "ward" >= ',"'8'",' AND "ward" <= ',"'9'")
# This query selects from the table with name "47350364-44a8-4d15-b6e0-5f79ddff9367"
# which corresponds to the Pittsburgh Playgrounds resource, which can be found here:
#      https://data.wprdc.org/dataset/playgrounds/resource/47350364-44a8-4d15-b6e0-5f79ddff9367

ds_search_sql(query_8_to_9, as = "table") # Looking for "ward" values between "8" and "9"
# Running this gives four playgrounds: three in ward 8 and one in ward 9.

query_9_to_10 = paste0('SELECT * FROM "47350364-44a8-4d15-b6e0-5f79ddff9367" WHERE "ward" >= ',"'9'",' AND "ward" <= ',"'10'")
ds_search_sql(query_9_to_10, as = "table") # Looking for "ward" values between "9" and "10"
# Running this query gives zero playgrounds because ward is a string, 
# and there are no values between '9' and '10' when searching alphabetically 
# ('9' comes after '10').

# To fix this, we have to convert the ward field (which is deliberately a string
# since it's an identifier (like a ZIP code), and nothing prevents new ones from
# popping up with characters in them (just like ZIP codes have been expanded, so
# sometimes you see the ZIP+4 format)) to an integer.

# We can do this in SQL with the CAST function.
corrected_query_9_to_10 = paste0('SELECT * FROM "47350364-44a8-4d15-b6e0-5f79ddff9367" WHERE CAST("ward" as integer) >= 9 AND CAST("ward" as integer) <= 10')
ds_search_sql(corrected_query_9_to_10, as = "table") # Running this query returns
# eight results, for the seven playgrounds in ward 10 and the one playground in ward 9.