What is the Syntax to substr in Oracle to subtract a string i have "123456789 #073" I only want what after the #
substr (table.col, 17,3) is that ok ?
2 Answers
Most likely the simplest (and most performant) way of doing this would be to use the base string functions:
SELECT SUBSTR(col, INSTR(col, '#') + 1)
FROM yourTable;
We could also try using REGEXP_REPLACE here:
SELECT REGEXP_REPLACE(col, '.*#(.*)', '\1')
FROM yourTable;
The regex option would in general not perform as well as the first query. The reason for this is that invoking a regex incurs a performance overhead. You might want to consider a regex option if you expect that the string logic might change or get more complicated in the future. Otherwise, go with base string functions wherever possible.
I think the most direct method might be regexp_substr():
select regexp_substr('123456789 #073', '[^#]+$')
from dual;
The regular expression says: "get me all non-hash characters at the end of the string".
If you happen to know that there are 3 characters and really want the last three characters of the string:
select substr('123456789 #073', -3)
Related