CODEWITHSUNDEEP
sqlplsqloracle11g
user2924127
user2924127

Reputation: 6240

remove slash depending on values

I have a column whos value is combination of two values:

(SELECT value FROM table where id = 'some_unique_id') || '/' || other_table.somecolumn

These two values can both have values, one may have a value, or none of them can have a value. If both have none value currently '/' is being displayed, but if both don't have a value I would like a blank to appear. If other_table.somecolumn has no value then it is being displayed as [firstvalue]/, but I would like ti without the / so it would just show the first value. Lastly if the first value has no value it is being displayed as /[secondvalue], but again I would like the / to be removed and only show the second value. I am unsure how to do this, I have tried to play around with the decode function, but can;t get it to work.

Upvotes: 0

Views: 63

Answers (1)

Alex Poole
Alex Poole

Reputation: 191295

You can either add some logic to decide whether to add the slash at all - which is complicated by the subquery, if that is really necessary - or you can trim() any leading or trailing slashes:

select trim(both '/' from
  (SELECT value FROM table where id = 'some_unique_id') || '/' || other_table.somecolumn)
from ...

Although that assumes your two values don't themselves start or end with slashes - if they do then those could be removed too. If you're using the slash as a kind of delimiter that would be confusing anyway.

Quick demo:

with t (str) as (
  select '/second' from dual
  union all select 'first/' from dual
  union all select '/' from dual
  union all select 'first/second' from dual
  union all select '/first/second/' from dual
)
select trim('/' from str) from t;

TRIM('/'FROMST
--------------
second        
first         

first/second  
first/second

Upvotes: 2

Related Questions