Oracle SQL 加载具有特殊字符的表列
我正在将数据加载并插入到 Oracle 数据库中。当我遇到看起来像汉字的特殊字符时,我收到类似行被拒绝的错误,因为超出了列的最大大小。对于具有英文字符且同一列的长度似乎相同的行,我没有收到此错误。我正在使用 SUBSTR 和 TRIM 函数,但它不起作用。如何判断中文字符串的长度是否超过列大小?
I am loading and inserting data into an Oracle database. When I encounter special characters that look like Chinese characters, I am getting an error like row rejected because maximum size of column was exceeded. I am not getting this error for rows which have English characters which appear to be of same length for same column. I am using SUBSTR and TRIM function but it is not working. How can I determine whether the length of a string which is in Chinese exceeds column size?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
如果您的列定义为
VARCHAR2(XX)
[例如VARCHAR2(20)
],则当您尝试插入大于 XX 的字符串时,您将收到错误字节长。函数
SUBSTR
以字符数计算长度,而不是字节。要选择以字节为单位的子字符串,请使用函数SUBSTRB
。编辑:按照Adam的建议,如果定义列,则可以使用字符算术变量为
VARCHAR2 (XX CHAR)
。在这种情况下,您的列将能够存储所有字符集中的 XX 个字符(如果将其存储在表中,则最多可达 4000 字节)。if your columns are defined as
VARCHAR2(XX)
[for exampleVARCHAR2(20)
], you will receive an error if you try to insert a string that is more than XX bytes long.The function
SUBSTR
calculates length in number of characters, not bytes. To select a substring in bytes, use the functionSUBSTRB
.Edit: As suggested by Adam, you can use character arithmetics if you define your columns and variables as
VARCHAR2 (XX CHAR)
. In that case your columns will be able to store XX characters, in all character sets (up to a maximum of 4000 bytes if you store it in a table).