0

What function can I use to get rid of certain characters within a string? I know I can use the REPLACE() function but there are around 30 variables which will need doing so hoping for a more efficient route.

Example:

What it currently shows: 

BasicPay_5000
CarAllowance_6000
Bonus_7000

What I need it to show: 

5000
6000
7000

Is there a function that removes all characters up to the pre-defined character? E.g. by writing a statement which removes all characters up to _ (underscore).

jarlh
  • 42,561
  • 8
  • 45
  • 63
A.Leggett
  • 61
  • 3
  • 10
  • Please, check out the http://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string article. This may be usefull. – rchrd Apr 06 '17 at 14:13

2 Answers2

1

This should help

select substr('BasicPay_5000', instr('BasicPay_5000','_') + 1) from dual

BasicPay_5000 can be replaced with any data you have, if its in the said format

Sudipta Mondal
  • 2,550
  • 1
  • 19
  • 20
1

This uses a regular expression to get the numbers at the end of the string.

[0-9] - any digit

{1,} - 1 or more of the previous search term

$ - anchor the search to the end of the line.

SELECT REGEXP_SUBSTR('Basic_Pay_5000','[0-9]{1,}$') FROM dual
BriteSponge
  • 1,034
  • 9
  • 15