CODEWITHSUNDEEP
sqlteradata
Charlie
Charlie

Reputation: 11

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.

I tried running this:

    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.

This is in Teradata

TABLE A

Column 1 Column 2
Joey Level 2
Rachel Level 4
Ross Level 30
Pheobe Level 2
Monica Level 3
Chandler Level 20

RESULT should be only: Joey, Rachel, Phoebe and Monica since these have less than Level 15.

////

Column 1 Column 2
Joey Level 2
Rachel Level 4
Pheobe Level 2
Monica Level 3

Upvotes: 0

Views: 263

Answers (3)

dnoeth
dnoeth

Reputation: 60462

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 returns NULL for bad data instead of failing.

Upvotes: 1

Abak
Abak

Reputation: 41

First. Thank you for posting your question here. I didn't have the need to use CAST before, but it's a very useful operator.

The solution might be much simpler than you think. Change all-uppercase 'LEVEL ' to 'Level ' and give it a try.

There are other alternatives that you may want to try:

  1. Replace CAST with CAST(TO_NUMBER(REGEXP_REPLACE(game_progress, 'Level ', '')) AS INTEGER)
  2. Use SPLIT_PART(string, delimiter, position) instead of REGEXP_REPLACE, like this CAST(SPLIT_PART(game_progress, ' ', 2) AS INTEGER)

This is what I tried using PostgreSQL: enter image description here

Of course, your query is much more complex, but the idea is there. See what happens when I try with all-uppercase 'LEVEL '. enter image description here

Sometimes error messages are misleading and that's why you had a hard time finding what was wrong with your query.

IMPORTANT: My other suggestion is that you should restructure your columns. The 'Level' column, for example have 'Level ' defined on every line. You could simply have a column named 'Level' that accepts integers, and then insert/update with integers. Then, all your queries can work without the need of complex operators.

I hope that this was helpful.

Have a great day!

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

could be you have some hidden spaces and or case sensitive issue

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(loweer(TRIM(game_progress)), 'level', '') as INTEGER)) < 15

Upvotes: 0

Related Questions