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

Question

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!

I'm not Oracle expert, but I believe strtok() exists (Correct me if I'm wrong). SELECT item, strtok(item, '&', 1) as Trimmed_Item FROM items; should do the job. — JNevill
– JNevill, Commented Jul 18, 2022 at 14:46
Unfortunately I'm receiving an invalid identifier error for strtok(). Doesn't look like I can use that function. — Tyler Fink
– Tyler Fink, Commented Jul 18, 2022 at 15:01
use substr(item, 1, instr(item, '&')-1) and add a where clause item like '%&%'. Will be an inefficient query if the number of rows is exploding. In that case change your design. Do the processing while inserting the data. — Ali Avcı
– Ali Avcı, Commented Jul 18, 2022 at 15:29
Adding the where clause gives me all rows with an "&" but does not return the other set of data I'm looking for where the row is like the "&" item without the "&" character and characters after. I'll update my query above, thanks. — Tyler Fink
– Tyler Fink, Commented Jul 18, 2022 at 16:14

Massi FD · Accepted Answer · 2022-07-18 16:12:48Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related