英文:
Prevent extra space being inserted into a CHAR field?
问题
我使用以下脚本在MS Access中创建表格：
CREATE TABLE POWERSQL (
ProposalNumber INTEGER PRIMARY KEY,
FirstName CHAR (15),
LastName CHAR (20),
Address CHAR (30),
City CHAR (25),
StateProvince CHAR (2),
PostalCode CHAR (10),
Country CHAR (30),
Phone CHAR (14),
HowKnown CHAR (30),
Proposal CHAR (50),
BusinessOrCharity CHAR (1) );
当向一个CHAR字段（如[FirstName]）插入比设置长度短的值，例如'John'，它会用空格填充剩余的字符，这会影响其他查询和连接。Trim函数无法解决。有什么建议吗？非常感谢。
英文:
I create a table in MS Access using the following script:
CREATE TABLE POWERSQL (
ProposalNumber INTEGER PRIMARY KEY,
FirstName CHAR (15),
LastName CHAR (20),
Address CHAR (30),
City CHAR (25),
StateProvince CHAR (2),
PostalCode CHAR (10),
Country CHAR (30),
Phone CHAR (14),
HowKnown CHAR (30),
Proposal CHAR (50),
BusinessOrCharity CHAR (1) );
When insert some value to a CHAR filed which is shorter than the set length, e.g., 'John' in [FirstName], it fills the remaining characters with empty space which messes up with other queries and joins. Trim does not help. Any advice? Many thanks.
答案1
得分: 3
CHAR字段在MS Access中是半支持的，且具有固定长度。这意味着您不能插入少于完整字段长度的字符，如果这样做，Access将用空格填充未使用的位置。
相反，对于可变长度字符字段，请使用VARCHAR：
CREATE TABLE POWERSQL (
ProposalNumber INTEGER PRIMARY KEY,
FirstName VARCHAR (15),
etc...
英文:
CHAR fields are half-supported in MS Access and are fixed-length. This means you can't insert less characters than the full field length, and if you do, Access will fill the unused positions with spaces.
Instead, use VARCHAR for variable-length character fields:
CREATE TABLE POWERSQL (
ProposalNumber INTEGER PRIMARY KEY,
FirstName VARCHAR (15),
etc...
答案2
得分: 0
Use TEXT 数据类型代替固定长度的 CHAR(x) 数据类型，如下所示：
FirstName TEXT,
..................
TEXT 数据类型是可变长度的，可存储最多 255 个字符。
英文:
Instead of the fixed length CHAR(x) data type, use TEXT, like:
FirstName TEXT,
.................
The TEXT data type is variable length and can store up to 255 characters
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论