How to filter splitting a column value?

Question

I´m a newbie in the SQL world and I code with just the simple insert, select, delete and update.

So... I have a table with a column named "market" that has a list of cryptocoins... like this...

market
-------
EOS/ETH    
EOS/BTC
XMR/BTC
LTC/BTC
DASH/ETH
...

I need to select * from this table where:

• the string before the '/' is equal to each other but after the '/' goes ETH and BTC.
• There is a column named exchange that I want to filter too. In this example would be all this coins where exchange = 'bittrex'

In this example the result would be:

EOS/ETH 
EOS/BTC

Can anyone help?

Answer 1

You can use string functions like INSTR(), LEFT(), RIGHT(), LENGTH() for this purpose.

create table market(id int, col varchar(100), exchange varchar(100));

insert into market values
(1, 'EOS/ETH',  'bittrex'),
(2, 'EOS/BTC',  'other'),
(3, 'EOS/BTC',  'bittrex'),
(4, 'XMR/BTC',  'other'),
(5, 'LTC/BTC',  'other'),
(6, 'DASH/ETH', 'other');

select *
from   market t1
where  right(t1.col, length(t1.col) - instr(t1.col, '/')) IN ('ETH','BTC')
and    exchange = 'bittrex'
and    exists (select 1
               from market t2
               where left(t2.col, instr(t2.col, '/')) = left(t1.col, instr(t1.col, '/'))
               and right(t2.col, length(t2.col) - instr(t2.col, '/')) IN ('ETH','BTC')
               and   exchange = 'bittrex'
               group by left(t2.col, instr(t2.col, '/'))
               having count(*) > 1)

id | col     | exchange
-: | :------ | :-------
 1 | EOS/ETH | bittrex 
 3 | EOS/BTC | bittrex 

dbfiddle here

As Rick James has pointed out on his comments you can use SUBSTRING_INDEX() and get the same result.

select *
from   market t1
where  substring_index(t1.col, '/', -1) IN ('ETH','BTC')
and    exchange = 'bittrex'
and    exists (select   1
               from     market t2
               where    substring_index(t2.col, '/', 1) = substring_index(t1.col, '/', 1)
               and      substring_index(t1.col, '/', -1) IN ('ETH','BTC')
               and      exchange = 'bittrex'
               group by substring_index(t1.col, '/', -1)
               having   count(*) > 1)

id | col     | exchange
-: | :------ | :-------
 1 | EOS/ETH | bittrex 
 3 | EOS/BTC | bittrex 

dbfiddle here

Answer 2

This REGEXP is another simplification.

SELECT * from tbl
    FROM ( SELECT market, exchange
                 SUBSTRING_INDEX(col, '/', 1) AS lft
            FROM tbl
            WHERE col REGEXP "(ETH|BTC)$"
              AND exchange = 'bittrex'
            GROUP BY lft
            HAVING COUNT(*) > 1
         ) AS x
    JOIN tbl  USING (market, exchange)