RSQLite操作SQLite时如何将空格分隔的字符串列拆分为多列

split_part()是PostgreSQL等数据库的内置字符串函数，SQLite原生不支持该函数，所以执行UPDATE时函数返回NULL，最终X1~X23列全部为空值（即你看到的<NA>）。

方案1：R侧处理后回写（代码最简单，内存足够时效率最高） #

不需要写复杂的SQL拆分逻辑，直接把表读入R完成拆分，再写回数据库即可：

library(DBI)
library(RSQLite)
library(tidyr)

# 建立数据库连接
con <- dbConnect(SQLite(), dbname = 'ShearTest3.sqlite')

# 读取全表数据
sh3_df <- dbReadTable(con, "sh3")

# 按空格拆分指定列为23个独立列，拆分后自动移除原字符串列
sh3_df <- separate(
  sh3_df,
  col = `Rockable 29-11-2018`,
  into = paste0("X", 1:23),
  sep = " ",
  remove = TRUE
)

# 覆盖写回数据库
dbWriteTable(con, "sh3", sh3_df, overwrite = TRUE)

# 用完断开连接
dbDisconnect(con)

如果单表数据量太大内存装不下，可以分批读取、拆分、批量更新，避免内存溢出。

方案2：纯SQLite语句实现（适合超大数据量，无需加载数据到R内存） #

通过SQLite内置的instr()定位空格位置、substr()截取字符串，不需要依赖R侧计算，执行效率很高。拆分前2列的UPDATE语句写法如下，剩余X3~X23按相同的嵌套截取逻辑递推即可：

UPDATE sh3
SET
  -- 截取第一个空格前的内容为X1
  X1 = substr(`Rockable 29-11-2018`, 1, instr(`Rockable 29-11-2018`, ' ') - 1),
  -- 截取第一个空格和第二个空格之间的内容为X2
  X2 = substr(
    `Rockable 29-11-2018`,
    instr(`Rockable 29-11-2018`, ' ') + 1,
    instr(substr(`Rockable 29-11-2018`, instr(`Rockable 29-11-2018`, ' ') + 1), ' ') - 1
  )
  -- 后续列每次嵌套一层substr+instr，定位到对应位置的空格后截取即可
;
-- 所有列更新完成后，删除原字符串列
ALTER TABLE sh3 DROP COLUMN `Rockable 29-11-2018`;

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