chop part of the string

[dba_admin]

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.

[hacketta1]

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

[marist89]

SQL> l
  1  select
  2  substr('xyxxyyxxxyyyxy',
  3     instr('xyxxyyxxxyyyxy','xy')+length('xy'),
  4     length('xyxxyyxxxyyyxy'))
  5* from dual
SQL> /

SUBSTR('XYXX
------------
xxyyxxxyyyxy

[rad_jen]

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

[bhallar]

By rad_jen
SQL> select substr('xyxxyyxxxyyyxy',-12,12) from dual;

If you really want to use hard code value then why not simply
substr('xyxxyyxxxyyyxy', 3) or is there any specific reason.

[kris109]

SQL> select replace('xxyyxxxyyyxy', 'xxyyxxxyyyxy', 'xxyyxxxyyy') from dual;

REPLACE('X
----------
xxyyxxxyyy

[jmodic]

select
  replace(substr(:my_string,1,2),'xy') ||
  substr(:my_string,3)
from dual;

[dba_admin]

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.

[kris109]

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.