Dumb ML
Dumb ML

Reputation: 367

How can I get all rows where just part of a string repeats?

I'm working in SQL (PostgreSQL) with a dataset where each row is a product the company sold and each column contains some information about the product, like price, size, etc. The problem here is that I have a few types of products: (i) products that are only sold outside my state; (ii) products that are sold only inside the state; (iii) products that may be sold both inside or outside the state; (iv) products that are not specified. To specify where the product may be sold, there is extra strings. We use "-IN" if we sell inside the state and "-OUT" if we sell outside the state. Besides this issue, I can't use only split_part() because there might be more than one '-' In other words, I have something like this:

product_name        units        price
water-out           5            50
water-out           2            20
water-in            3            21
apple-red-in        7            7
ice-out             4            2
wood-out            12           120
leather             17           6
wood-in             1            10
tuna                5            25
it-br-in            1            2
it-br-out           5            25

I want to filter only the products that are sold both inside and outside the state. Products that we have both the possibility of having '-in' and '-out' in the name. I want to get this result:

product_name        units        price
water-out           5            50
water-out           2            20
water-in            3            21
wood-out            12           120
wood-in             1            10
it-br-in            1            2
it-br-out           5            25

How can I do this?

Upvotes: 0

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

This answers the original version of the question.

Assuming that the product name has no hyphens and the only suffixes are "-in" and "-out", then split_part() can do this:

select t.*
from t
where exists (select 1
              from t t2
              where split_part(t2.name, '-', 1) = split_part(t.name, '-', 1) and
                    t2.name <> t.name
             )

Here is a db<>fiddle.

Upvotes: 1

Related Questions