如何获取设置为字符串的列的值? [英] How can I grab a value of a column that is set as a string?

问题描述

我有一个表格，其中包含一个包含级别列表的列.此列的数据类型为字符串.我试图将级别的值作为整数来获取，以便为我提供级别低于特定数字的人员列表.

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.

这篇关于如何获取设置为字符串的列的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！