SQL错误:[2620:HY000]格式或数据包含错误字符 [英] SQL error: [2620 : HY000] The format or data contains a bad character

问题描述

game_progress 列的类型为 varchar .它包含如下值:

The column game_progress is of type varchar. It contains values like:

1级，10级，12级，20级

LEVEL 1, LEVEL 10, LEVEL 12, LEVEL 20

我希望WHERE条件仅查看此字符串值中的数字.

I want the WHERE condition to look at only the number in this string value.

我正在尝试在 Teradata DBMS 上执行以下SQL查询.

I am trying to execute following SQL query on Teradata DBMS.

SELECT user_id, CAST(REGEXP_REPLACE(game_progress, 'LEVEL ', '') as INTEGER)
FROM database1.vw_ft_rockroll a
INNER JOIN database1.vw_dim_rockroll b on a.progress_id = b.progress_id
WHERE user_key = 21111111 and game_type = 'LEVEL'
and CAST(REGEXP_REPLACE(game_progress, 'LEVEL ', '') as INTEGER) < 15

如果我不包括最后一个 where条件以<结尾的查询，则该查询有效15 .当我输入和CAST(REGEXP_REPLACE(game_progression，'LEVEL'，'')作为INTEGER)时<15 ，它给了我这个SQL错误:

The query works if I don't include the last where condition ending < 15. When I input and CAST(REGEXP_REPLACE(game_progression, 'LEVEL ', '') as INTEGER) < 15, it gives me this SQL error:

[2620:HY000]格式或数据包含错误字符

[2620 : HY000] The format or data contains a bad character

另一次尝试失败

如果在添加和CAST(REGEXP_REPLACE(game_progress，'LEVEL'，'')作为INTEGER之前，我只尝试平均计算其级别数，则<15 .添加此代码后，会出现以下错误:

Another attempt fails

Also if I only try to average the level numbers it works before I add and CAST(REGEXP_REPLACE(game_progress, 'LEVEL ', '') as INTEGER) < 15. Once I add this, I get the following error:

计算过程中发生数字溢出.

Numeric overflow occurred during computation.

SELECT AVG(CAST(REGEXP_REPLACE(game_progress, 'LEVEL ', '') as INTEGER)) FROM database1.vw_ft_rockroll a
INNER JOIN database1.vw_dim_rockroll b on a.progress_id  = b.progress_id 
WHERE user_key = 21111111 and game_progression_type = 'LEVEL'
and CAST(REGEXP_REPLACE(game_progress, 'LEVEL ', '') as INTEGER) < 15

有人知道如何解决此问题吗?

Does anyone know how to fix this?

推荐答案

我建议按照分析步骤进行解决:

1. 在您的DBMS文档(Teradata)中搜索错误
2. 在投射前检查值
3. 注意可能出现的边缘情况

在您的DBMS文档(Teradata)中搜索错误

您的错误消息:

[2620:HY000]格式或数据包含错误字符.

[2620 : HY000] The format or data contains a bad character.

可以在 Teradata文档:

此错误表示用户已提交了具有非法格式的数字到字符的转换，或者在字符到数字的转换中，数据或格式包含错误的字符.

This error indicates that the user has submitted a numeric-to-character conversion with an illegal format, or that, in a character-to-numeric conversion, the data or the format contains a bad character.

推荐的提醒是:

验证使用的格式.如果合法，则在从字符转换为数字的数据中存在非法字符.

Verify the format used. If it is legal, then an illegal character is present in the data being converted from character to numeric.

在投射前检查值

错误说明表明从文本到数字的广播期间出现问题.要解决此问题，请尝试在没有 casting 的情况下发生的情况:在您的SELECT列表中，删除 CAST ，然后只保留内部表达式，如:

Check the values before casting

The error explanation indicates a problem during casting from text to number. To isolate this problem, try what happens without (before) casting: In your SELECT list remove the CAST and just leave the inner expression like:

SELECT REGEXP_REPLACE(game_progress, 'LEVEL ', '')

这应该返回期望值，该期望值具有必需的格式(无错误字符)以允许转换为数字(即INTEGER).

This should return the expected values, that have the required format (without bad characters) to allow casting to a number (i.e. INTEGER).

要使 CAST(< expression>作为INTEGER)正常工作，要求< expression> 始终返回有效数字，正好是一个以文本表示的INTEGER

To make CAST(<expression> as INTEGER) working, it is required that <expression> always returns a valid number, exactly an INTEGER represented as text.

但是您实际上得到了什么呢?我们假设(根据您的字段描述给出)以下值:

But what do you actually get as result? We assume (as given by your field description) following values:

• LEVEL 1 被REGEXP替换为 1
• 10级被REGEXP替换为 0
• LEVEL 12 被REGEXP替换为 12
• LEVEL 20 被REGEXP替换为 20

• LEVEL 1 replaced by REGEXP to 1
• LEVEL 10 replaced by REGEXP to 0
• LEVEL 12 replaced by REGEXP to 12
• LEVEL 20 replaced by REGEXP to 20

NULL 值或空文本怎么样?格式错误的文本不符合 LEVEL n 格式怎么办?

What about NULL values or empty text ? What about malformed text not conforming the LEVEL n format?

这篇关于SQL错误:[2620:HY000]格式或数据包含错误字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！