5

In a table column, I have this value:

DV-2011-01-000004 (the data type is varchar2)

How can i get the part of the string '000004'? In t-sql they have this right() function but in PL SQL i cant'seem to find a function just like the right() in t-sql.

Thanks for the help.

FMiS Help Desk
  • 75
  • 1
  • 2
  • 6
  • 2
    In order that this question may help future developers: please edit your question to state exactly what your requirement was - do you want to get the string of characters after the last '-', or did you want to just get the rightmost 6 characters? – Jeffrey Kemp Feb 28 '12 at 06:54

2 Answers2

8
substr('DV-2011-01-000004', length('DV-2011-01-000004')-6 + 1 )
turbanoff
  • 2,439
  • 6
  • 42
  • 99
  • Ok let him decide, I'm generally for general solutions and not specific ones. – Dor Cohen Feb 27 '12 at 07:26
  • We can not know exactly what decision they need. May be my solution is general – turbanoff Feb 27 '12 at 07:27
  • Since OP did not specify how to recognize the last part of the string (fixed length or character) this answer is as "general" as the other. – Rene Feb 27 '12 at 13:47
2

you can use: 

 SUBSTR('DV-2011-01-000004', INSTR('DV-2011-01-000004', '-', -1) +1)

when using INSTR with negative start position he will find the last index of "-". then SUBSTR will cut from this occurrence until the end (because I didn't supply Length)

Dor Cohen
  • 16,769
  • 23
  • 93
  • 161