Search code examples
sqlrpostgresqlwhere-clausesql-like

LIKE with integers in PostgreeSQL using R

I need to download a table from postgree to R, but filtered by part of an INT.

I have been trying:


library(RPostgreSQL)


con <- dbConnect(PostgreSQL(), user= "#####", dbname="######",password="#####"  
                 ,host="#####", port='######')                         

vetor_id <- c("83052407","10406587","12272377")

match_id <- dbGetQuery(con,paste("
                                         SELECT *
                                         FROM public.data2015 
                                         WHERE  id IN ('", paste(vetor_id,collapse = "','"),"')
                                        ",sep = ""))

dbDisconnect(con)

I also tried CONTAINS but didn't work.WHERE Contains(id,", paste(vetor_id,collapse = " OR "),"')

id is INT and vetor_id is just part of the values. I mean,vector_id = 83052407 must find id = 83052407000132.

How can I use something like LIKE and put vetor_id% ?

Solution

  • Is this what you want?

    WHERE id::text like ? || '%'

    This converts the integer id to a string, and attempts to match it against the parameter. If id starts with the parameter, the condition is satisfied.

    Note that this uses a legitimate query parameter (represented by the question mark): you should get used to parameterize your queries rather than concatenating variables in the query string: this is cleaner, more efficient and safer.