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

User_X872A

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;

Paulzip

Please provide sample data.

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

Is not valid in a query in your context.

Frank Kulash

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

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