如何用SQLite语句标准化谱系普查信息的字符串格式？

针对你需要统一Relationship和Occupation字段后文本格式的需求，这里提供纯SQLite语句实现方案，无需额外编程：

核心逻辑 #

将目标文本转换为首词首字母大写、其余字母小写的格式，通过SQLite的字符串函数组合实现：

• 用INSTR()定位关键词的位置
• 用SUBSTR()提取需要格式化的文本片段
• 用UPPER()+LOWER()实现首字母大写、其余小写的转换
• 用REPLACE()将格式化后的文本替换回原字段

步骤1：先测试格式化效果（安全验证） #

先执行SELECT语句查看转换后的结果，确保符合预期：

SELECT 
  description,
  -- 先处理Relationship部分，再处理Occupation部分
  REPLACE(
    REPLACE(
      description,
      -- 提取Relationship后的内容（从"Relationship: "后到" Occupation: "前）
      SUBSTR(description, INSTR(description, 'Relationship: ') + 14, INSTR(description, ' Occupation: ') - (INSTR(description, 'Relationship: ') + 14)),
      -- 格式化：首字母大写，其余小写
      UPPER(SUBSTR(SUBSTR(description, INSTR(description, 'Relationship: ') + 14, INSTR(description, ' Occupation: ') - (INSTR(description, 'Relationship: ') + 14)), 1, 1)) 
      || LOWER(SUBSTR(SUBSTR(description, INSTR(description, 'Relationship: ') + 14, INSTR(description, ' Occupation: ') - (INSTR(description, 'Relationship: ') + 14)), 2))
    ),
    -- 提取Occupation后的内容（从"Occupation: "后到句号前）
    SUBSTR(description, INSTR(description, 'Occupation: ') + 12, INSTR(description, '.', INSTR(description, 'Occupation: ')) - (INSTR(description, 'Occupation: ') + 12)),
    -- 格式化：首字母大写，其余小写
    UPPER(SUBSTR(SUBSTR(description, INSTR(description, 'Occupation: ') + 12, INSTR(description, '.', INSTR(description, 'Occupation: ')) - (INSTR(description, 'Occupation: ') + 12)), 1, 1)) 
    || LOWER(SUBSTR(SUBSTR(description, INSTR(description, 'Occupation: ') + 12, INSTR(description, '.', INSTR(description, 'Occupation: ')) - (INSTR(description, 'Occupation: ') + 12)), 2))
  ) AS standardized_description
FROM census_records; -- 替换为你的表名

步骤2：执行更新操作 #

确认测试结果正确后，执行UPDATE语句修改数据（务必先备份数据库，避免不可逆错误）：

UPDATE census_records -- 替换为你的表名
SET description = 
  REPLACE(
    REPLACE(
      description,
      SUBSTR(description, INSTR(description, 'Relationship: ') + 14, INSTR(description, ' Occupation: ') - (INSTR(description, 'Relationship: ') + 14)),
      UPPER(SUBSTR(SUBSTR(description, INSTR(description, 'Relationship: ') + 14, INSTR(description, ' Occupation: ') - (INSTR(description, 'Relationship: ') + 14)), 1, 1)) 
      || LOWER(SUBSTR(SUBSTR(description, INSTR(description, 'Relationship: ') + 14, INSTR(description, ' Occupation: ') - (INSTR(description, 'Relationship: ') + 14)), 2))
    ),
    SUBSTR(description, INSTR(description, 'Occupation: ') + 12, INSTR(description, '.', INSTR(description, 'Occupation: ')) - (INSTR(description, 'Occupation: ') + 12)),
    UPPER(SUBSTR(SUBSTR(description, INSTR(description, 'Occupation: ') + 12, INSTR(description, '.', INSTR(description, 'Occupation: ')) - (INSTR(description, 'Occupation: ') + 12)), 1, 1)) 
    || LOWER(SUBSTR(SUBSTR(description, INSTR(description, 'Occupation: ') + 12, INSTR(description, '.', INSTR(description, 'Occupation: ')) - (INSTR(description, 'Occupation: ') + 12)), 2))
  );

注意事项 #

• 该语句假设你的描述文本格式统一为Relationship: X. Occupation: Y.，如果存在格式变体（比如缺少Occupation、标点不同），需要调整INSTR()的定位逻辑
• 执行UPDATE前一定要备份数据库，或者先创建临时表测试更新结果
• 对于Brother in law这类多词文本，该语句会将首词首字母大写，其余单词全小写，最终得到Brother in law，符合你要求的格式统一需求

内容的提问来源于stack exchange，提问作者Ali Christie-Upton