Access数据库表-拆分字段SQL命令 [英] Access DB Table - Split Field SQL Command

问题描述

我有一个Access 2013数据库表dbo_GOV

I have an Access 2013 Database Table, dbo_GOV

目标

我要使用USSenators字段，该字段包含类似(如下)的数据，并将其分别分为USSenator1和USSenator2字段:

I want to take the USSenators field, which contains data like (below) and split it into USSenator1 and USSenator2 fields, respectively:

John Smith;Sarah Levens
Bill Burr;Kevin Nill
George Thomson;Tracy Johnson

---

问题

我尝试了几种不同的Access SQL查询...在执行时都(在下面)都给出错误消息

I've tried a few different Access SQL queries... both (below) when executed, give the error message

无效使用'.'，'！'或'()'.在查询表达式中 'Split(USSenators&;"，';')(0'.

Invalid use of '.', '!', or '()'. in query expression 'Split(USSenators & ";", ';')(0'.

我已经验证了有0条记录，其中USSenators为空白.每行列出了2个人，中间用分号隔开.

I have verified that there are 0 records, where USSenators is blank. Every row has 2 people listed, separated by a semicolon.

SQL查询

UPDATE dbo_GOV
SET
    USSenator1 = Split(USSenators & ";",';')(0),
    USSenator2 = Split(USSenators & ";",';')(1);

---

UPDATE dbo_GOV
SET
   USSenator1 = Split(USSenators,';')(0),
   USSenator2 = Split(USSenators,';')(1);

---

我尝试引用Office文档中的Split: 这里

---

I've tried referencing the Office documentation for Split: here

推荐答案

您不能在查询中使用拆分，而请使用Mid和Instr.

You cannot use Split in a query, use Mid and Instr.

Mid(USSenators,Instr(USSenators,";")+1)
Mid(USSenators,1,Instr(USSenators,";")-1)

上面的第2行返回John Smith获得第一条记录
上面的第一行返回Sarah Levens作为第一条记录

Line 2 above returns John Smith for the first record
Line 1 above returns Sarah Levens for the first record

您需要注意空值.

这篇关于Access数据库表-拆分字段SQL命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！