Forum Stats

  • 3,838,560 Users
  • 2,262,382 Discussions
  • 7,900,688 Comments

Discussions

Hi, How to remove symbol '+' from single value (+DEF|IJK|XYZ|MNO) below query in oracle?

User_X872A
User_X872A Member Posts: 1 Green Ribbon

Kindly help on this logic function

result should be "DEF|IJK|XYZ|MNO" in single value

When i trying this way getting "missing right parenthesis"..

select col1,

    (substr(col2, 1,1)='+','|' 

   || substr(col2, greatest(-LENGTH(col2),-LENGTH(RTRIM(col2))-1))||'|',

    (substr(col2,1,1)='-','~|'||substr(col2,greatest(-length(col2),-length(rtrim(col2))-1))||'|','any'))

as col2

from table;

Answers

  • Paulzip
    Paulzip Member Posts: 8,720 Blue Diamond

    Please provide sample data.

    substr(col2, 1,1)='+'

    Is not valid in a query in your context.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,243 Red Diamond
    edited Aug 3, 2022 9:34PM

    Hi, @User_X872A

    To remove all '+' signs from a string, you can use:

    REPLACE (str, '+')
    

    That does what you requested for the given sample data. If you have more complicated requirements, then explain what they are. As Paulzip said, post a little sample data (5 rows may be enough in this case but 1 row is always too little) and the exact results you want from that sample data.

  • Stax
    Stax Member Posts: 130 Bronze Badge

    judging by the select, not only replacement + is needed

    SQL> ed
    Wrote file afiedt.buf
    
    
      SQL> ed
    Wrote file afiedt.buf
    
    
      1  with t as (
      2  select '+DEF|IJK|XYZ|MNO' col2 from dual union all
      3  select '-DEF|IJK|XYZ|MNO' col2 from dual union all
      4  select '-DEF|IJK|                 ' col2 from dual union all
      5  select '!xxx|yyy|zzz' col2 from dual
      6  )
      7  select col2,
      8  case substr(col2, 1,1)
      9    when '+'  then substr(col2, greatest(-LENGTH(col2),-LENGTH(RTRIM(col2))-1))||'|'
     10    when '-' then '~|'||substr(col2,greatest(-length(col2),-length(rtrim(col2))-1))||'|'
     11    else 'any'
     12    end  msg
     13* from t
    SQL> /
    
    
    COL2                       MSG
    -------------------------- -----------------------------
    +DEF|IJK|XYZ|MNO           +DEF|IJK|XYZ|MNO|
    -DEF|IJK|XYZ|MNO           ~|-DEF|IJK|XYZ|MNO|
    -DEF|IJK|                  ~|          |
    !xxx|yyy|zzz               any