问题描述
我有一个表格,其中包含一个包含级别列表的列.此列的数据类型为字符串.我试图将级别的值作为整数来获取,以便为我提供级别低于特定数字的人员列表.
I have a table that contains a column with a list of levels. This column has a data type of string. I am trying to grab the value of the level as an integer to give me the list of of people that have a level under a certain number.
我试过运行这个:
SELECT ukey, game_progress
FROM point_review.vw_ft_level_progress a
INNER JOIN point_review.vw_dim_level_progress b on a.game_progress_key = b.game_progress_key
where ukey = 2111222 AND game_progression_type = 'Level'
and (CAST(REGEXP_REPLACE(game_progress, 'LEVEL ', '') as INTEGER)) < 15
但是,我不断收到错误消息:格式或数据包含错误字符.
However, I keep getting an error that says: The format or data contains a bad character.
这是在 Teradata 中
This is in Teradata
表A
第 1 列 | 第 2 列 |
---|---|
乔伊 | 2 级 |
瑞秋 | 第 4 级 |
罗斯 | 30级 |
菲奥比 | 级别 2 |
莫妮卡 | 级别 3 |
钱德勒 | 20 级 |
RESULT 应该只有:Joey、Rachel、Phoebe 和 Monica,因为他们的等级低于 15.
RESULT should be only: Joey, Rachel, Phoebe and Monica since these have less than Level 15.
////
第 1 列 | 第 2 列 |
---|---|
乔伊 | 2 级 |
瑞秋 | 第 4 级 |
菲奥比 | 级别 2 |
莫妮卡 | 级别 3 |
推荐答案
RegEx 默认区分大小写,切换到 REGEXP_REPLACE(game_progress, 'LEVEL ', '', 1, 1, 'i')
使其不区分大小写.
RegExes are case sensitive by default, switch to REGEXP_REPLACE(game_progress, 'LEVEL ', '', 1, 1, 'i')
make it case insensitive.
如果仍然失败,您将获得其他非数字数据.使用它来查找它:
If it's still failing you got other non-numeric data. Use this to find it:
SELECT DISTINCT game_progress
FROM point_review.vw_ft_level_progress a
where TO_NUMBER(REGEXP_REPLACE(game_progress, 'LEVEL ', '', 1, 1, 'i')) IS NULL
TO_NUMBER
为坏数据返回 NULL 而不是失败.
TO_NUMBER
returns NULL for bad data instead of failing.
这篇关于如何获取设置为字符串的列的值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!