Menu
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.
237
Oracle SQL doesn't have LEFT and RIGHT functions. They can be emulated with SUBSTR and LENGTH.
The PLSQL INSTR function is used for returning the location of a substring in a string. The PLSQL INSTR function searches a string for a substring specified by the user using characters and returns the position in the string that is the first character of a specified occurrence of the substring.
The Oracle INSTR function is used to search string for substring and find the location of the substring in the string. If a substring that is equal to substring is found, then the function returns an integer indicating the position of the first character of this substring.
v_length Loop v_out := substr(IN_string,i,1) ; DBMS_OUTPUT. PUT_LINE(v_out); End loop; DBMS_OUTPUT. PUT_LINE('Text printed: ' || IN_string); End; -- Procedure created. BEGIN print_string('Hello'); END; -- Output: H e l l o Text printed: Hello Statement processed.
substr('DV-2011-01-000004', length('DV-2011-01-000004')-6 + 1 )
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)
39
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
