在R语言中提取指定SQL字符串里join后单词的最优实现方法

Great question! Extracting those table names from your SQL string is straightforward with regular expressions, and there are a couple of clean ways to do it in R—let's walk through the best options.

First, let's start with your input string:

db <- c("select * FROM a left join bd on bd.id=a.id left join ca on ca.id=a.id left join dc on dc.id=a.id where a.names != NULL")

Optimal Approach: Using stringr Package #

The stringr package (part of the tidyverse) makes string manipulation super intuitive, and it's my go-to for this kind of task. We'll use str_match_all to target the join keyword (case-insensitive, since SQL can use LEFT JOIN, join, etc.) and capture the table name that follows.

Here's the code:

library(stringr)

# Extract the captured table names after each 'join'
join_tables <- str_match_all(db, "(?i)join\\s+(\\w+)")[[1]][, 2]

# Check the result
join_tables
# Output: "bd" "ca" "dc"

How This Works: #

• (?i): Makes the regex case-insensitive, so it matches JOIN, Join, left join, etc.
• join\\s+: Matches the word "join" followed by one or more spaces.
• (\\w+): Captures one or more alphanumeric characters/underscores (your table name).
• str_match_all returns a matrix where each row is a match, and the second column is our captured table name—we just subset that column to get the final result.

Base R Alternative #

If you prefer not to load an extra package, you can do this with base R's gregexpr and regmatches:

# Find all matches using Perl-compatible regex
matches <- regmatches(db, gregexpr("(?i)join\\s+(\\w+)", db, perl = TRUE))[[1]]

# Strip out the "join " part to get just the table names
join_tables_base <- sapply(matches, function(x) sub("(?i)join\\s+", "", x, perl = TRUE))

join_tables_base
# Output: "bd" "ca" "dc"

Which Is Best? #

The stringr approach is the optimal choice for most cases—it's more readable, requires less boilerplate code, and avoids needing to post-process the matches like we do in base R. It's also consistent with modern R workflows if you're already using tidyverse tools.

Just a quick note: This regex assumes your table names are single "words" (no spaces or special characters beyond underscores). If you have more complex table identifiers (like quoted names), you'd need to adjust the regex, but it works perfectly for your example.

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