CODEWITHSUNDEEP
sqldatabaseoracle-database
KeyP
KeyP

Reputation: 5

SQL: extract numbers from string fields

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?

Upvotes: 0

Views: 177

Answers (2)

Giliam
Giliam

Reputation: 620

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

  • '3' becomes '000000003'
  • '40' becomes '000000040'
  • etc.

Sorting on the results above will lead to a correct ordering of numbers in a varchar2 datatype.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

If you want the numbers at the end of the string, you can use:

select replace(regexp_substr(cust_income_level, '[0-9,]+$'), ',', '')

Upvotes: 1

Related Questions