在R中使用sqldf过滤含引号字段的SQL数据库查询问题

Hey Aaron, let's work through why your filter queries aren't returning any results—you're right to suspect those double quotes are the culprit! Here are actionable steps to resolve this:

1. First, Verify if the Double Quotes Are Actually Stored in the Database #

Sometimes R adds double quotes when displaying string values, but they aren't actually present in the database. To check the real values:

# Fetch a small sample to inspect raw values
res <- dbSendQuery(npi2, " SELECT EntityTypeCode, ProviderBusinessPracticeLocationAddressStateName FROM master LIMIT 5 ")
sample_data <- fetch(res, n = 5)
# Print the raw values without R's default formatting
cat("EntityTypeCode values:\n")
print(sample_data$EntityTypeCode, quote = FALSE)
cat("\nState values:\n")
print(sample_data$ProviderBusinessPracticeLocationAddressStateName, quote = FALSE)
dbClearResult(res)

If the output shows values like 1 instead of "1", the quotes are just R's display formatting, and you need to look for other issues (like case sensitivity or typos in state names).

2. If Values Do Have Double Quotes in the Database #

If the sample confirms values include double quotes (e.g., "1"), adjust your filter to match the exact value, including the quotes. Here are two reliable ways to do this:

Option 1: Escape Quotes Manually #

You'll need to escape the double quotes within your SQL string in R so they're treated as part of the value:

# Filter for state 'PA' and EntityTypeCode '"1"'
res <- dbSendQuery(npi2, " SELECT * FROM master WHERE ProviderBusinessPracticeLocationAddressStateName = 'PA' AND EntityTypeCode = '\"1\"' ")
dbf_filtered <- fetch(res, n = -1) # Fetch all results
dbClearResult(res)

The \" syntax tells R to keep the double quote as part of the SQL query instead of closing the R string.

Option 2: Use Parameterized Queries (Recommended!) #

Parameterized queries are safer (prevents SQL injection) and automatically handle quote escaping, so you don't have to mess with manual formatting:

# Define your filter values exactly as they appear in the database
filter_state <- "PA"
filter_entity_type <- "\"1\"" # Or use '"1"' if you prefer that syntax

# Run the parameterized query
res <- dbSendQuery(npi2, 
                   " SELECT * FROM master WHERE ProviderBusinessPracticeLocationAddressStateName = ? AND EntityTypeCode = ? ",
                   params = list(filter_state, filter_entity_type))
dbf_filtered <- fetch(res, n = -1)
dbClearResult(res)

3. Quick Check for Case Sensitivity #

If quotes aren't the issue, some SQL databases (like PostgreSQL) are case-sensitive for string matches. Make sure your filter values match the exact case of the stored data (e.g., 'PA' vs 'pa').

内容的提问来源于stack exchange，提问作者Aaron