Report for clients with several conditions.
The result must contain 'upper_income_level' - the upper limit of the 'cust_income_level' column.
'cust_income_level' field has for example similar content: ‘G: 130,000 - 149,999’. How to extract only 149999 and convert to an integer?
If you want the numbers at the end of the string, you can use:
select replace(regexp_substr(cust_income_level, '[0-9,]+$'), ',', '')
Without regular expressions, you can use
substr( cust_income_level, instr( cust_income_level, '-') + 2 ) as cust_income_level
If you want to use it as numeric in an ORDER BY, you can use function TO_NUMBER(), but this is error prone since you'll get errors when the value isn't an actual number value.
to_number( substr( cust_income_level, instr( cust_income_level, '-') + 2 )) as cust_income_level
Instead, for the ORDER BY, you can add leading zero's and use that value to get the correct order. If there is a value which isn't a number, it will be presented in the results, but at least you won't get an error.
lpad( substr( cust_income_level, instr( cust_income_level, '-') + 2 ), 9, '0')
Example above will add leading zero's until the value is 9 characters long
Sorting on the results above will lead to a correct ordering of numbers in a varchar2 datatype.
