chop part of the string
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: chop part of the string

  1. #1
    Join Date
    Mar 2001
    Posts
    286

    chop part of the string

    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.

  2. #2
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    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

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Code:
    SQL> l
      1  select
      2  substr('xyxxyyxxxyyyxy',
      3     instr('xyxxyyxxxyyyxy','xy')+length('xy'),
      4     length('xyxxyyxxxyyyxy'))
      5* from dual
    SQL> /
    
    SUBSTR('XYXX
    ------------
    xxyyxxxyyyxy
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    May 2001
    Posts
    73
    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
    OCP 7.3,8.0,8i,9i

  5. #5
    Join Date
    Jun 2001
    Location
    California
    Posts
    124
    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.

  6. #6
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    SQL> select replace('xxyyxxxyyyxy', 'xxyyxxxyyyxy', 'xxyyxxxyyy') from dual;

    REPLACE('X
    ----------
    xxyyxxxyyy
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Code:
    select
      replace(substr(:my_string,1,2),'xy') ||
      substr(:my_string,3)
    from dual;
    Last edited by jmodic; 01-13-2004 at 04:12 PM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Mar 2001
    Posts
    286
    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.

  9. #9
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    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.
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width