CODEWITHSUNDEEP
sqloracle-database
Tyler Fink
Tyler Fink

Reputation: 1

How to select rows that are like other rows in the same table & column

I'm trying to find rows that contain the "&" character and are like the rows with the "&" character but do not contain any characters after and including the "&" character from the examples below. Both rows would be selected from the same item column in the items table. I would like both the rows with the "&" and the rows without the "&" returned. If no rows exist for the item without the "&", I still would like the row returned as blank or null.

What I have below currently selects the items with the "&" and selects the characters after and including the "&" character, but I need help figuring out how to find the rows that do not have the "&" character or characters after the "&" based on the data currently selected. This query is being run in Oracle.

item
abc&123
def&456
xyz&789
123456
123457 
select item, substr(item, instr(item, '&')) as Trimmed_Item 
from items where item like '%&%';

Current result:

item Trimmed_Item
abc&123 &123
def&456 &456
xyz&789 &789

Desired result:

item Trimmed_Item
abc&123 abc
def&456 def
xyz&789

Does anybody have an idea of how to do this? Thanks!

Upvotes: 0

Views: 169

Answers (1)

Massi FD
Massi FD

Reputation: 398

You can use this:

with items as 
     (
       select 'abc'||chr(38)||'123' as item from dual union all
       select 'def'||chr(38)||'456' as item from dual union all
       select 'xyz'||chr(38)||'789' as item from dual union all
       select '123456'              as item from dual union all
       select '123457'              as item from dual
     )
    select item, decode(regexp_substr(item, '[^&]+', 1, 1),item,' ',regexp_substr(item, '[^&]+', 1, 1)) as Trimmed_Item
      from items

ITEM    TRIMMED_ITEM
------- -------------
abc&123 abc    
def&456 def    
xyz&789 xyz    
123456         
123457

Thank you

Upvotes: 0

Related Questions