Is there a SQL function that can chop part of the string? For example, I want to chop 'xy' from the left side of 'xyxxyyxxxyyyxy'. The result is expected to be 'xxyyxxxyyyxy'. Please note TRIM or LTRIM do not work in this case.
Printable View
Is there a SQL function that can chop part of the string? For example, I want to chop 'xy' from the left side of 'xyxxyyxxxyyyxy'. The result is expected to be 'xxyyxxxyyyxy'. Please note TRIM or LTRIM do not work in this case.
To get the last x characters from the end of a string you can pass a negative value to SUBSTR e.g. -1. To get the first x characters from the beginning of a string pass a positive value. SUBSTR ('HELLO',1,2) would return 'HE'.
Not got Oracle with me, so can't give you a working example. If nobody has replied by the time I'm back in the office I'll post something that works.
HTH
Austin
Code:SQL> l
1 select
2 substr('xyxxyyxxxyyyxy',
3 instr('xyxxyyxxxyyyxy','xy')+length('xy'),
4 length('xyxxyyxxxyyyxy'))
5* from dual
SQL> /
SUBSTR('XYXX
------------
xxyyxxxyyyxy
SQL> select substr('xyxxyyxxxyyyxy',-12,12) from dual;
SUBSTR('XYXX
------------
xxyyxxxyyyxy
SQL> select substr('xyxxyyxxxyyyxy',2-length('xyxxyyxxxyyyxy'),length('xyxxyyxxxyyyxy')-2) from dual;
SUBSTR('XYXX
------------
xxyyxxxyyyxy
If you really want to use hard code value then why not simplyQuote:
By rad_jen
SQL> select substr('xyxxyyxxxyyyxy',-12,12) from dual;
substr('xyxxyyxxxyyyxy', 3) or is there any specific reason.
SQL> select replace('xxyyxxxyyyxy', 'xxyyxxxyyyxy', 'xxyyxxxyyy') from dual;
REPLACE('X
----------
xxyyxxxyyy
Code:select
replace(substr(:my_string,1,2),'xy') ||
substr(:my_string,3)
from dual;
This is the best one I can come up with.
SQL> select substr('xyxxyyxxxyyyxy',length('xy')+1) from dual;
SUBSTR('XYXXYYXXXYYYXY',LENGTH
-----------------------------------------------
xxyyxxxyyyxy
1 row selected.
Thank you for all your reply.
There are many ways to do the same thing in oracle as it should be.I got rid of the 'xy' from right not from the left but you get the idea.