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

Solution 1:

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:

SELECTDISTINCT game_progress
FROM point_review.vw_ft_level_progress a
where TO_NUMBER(REGEXP_REPLACE(game_progress, 'LEVEL ', '', 1, 1, 'i')) ISNULL

TO_NUMBER returns NULL for bad data instead of failing.

Solution 2:

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

SELECT ukey, game_progress
FROM point_review.vw_ft_level_progress a
INNERJOIN point_review.vw_dim_level_progress b on a.game_progress_key = b.game_progress_key
where ukey =2111222AND game_progression_type ='Level'and (CAST(REGEXP_REPLACE(loweer(TRIM(game_progress)), 'level', '') asINTEGER)) <15

Solution 3:

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:

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

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!