SQL extract data after character change MySQL

Question

I have the following data in a table, the column name is title:

Acqua Di Parma Blu Mediterraneo Arancia Di Capri Scented Water EDT
Acqua Di Parma Blu Mediterraneo Arancia
Acqua Di Parma Blu Mediterraneo Bergamotto Di Calabria
Acqua Di Parma Blu Mediterraneo Cipresso Di Toscana Scented Water EDT
Acqua di Parma Blu Mediterraneo fico di amalfi
Acqua Di Parma Blu Mediterraneo Fico di Amalfi Scented Water EDT
Acqua Di Parma Blu Mediterraneo Mirto di Panarea
Acqua Di Parma Blu Mediterraneo Mirto di Panarea Scented Water EDT
Acqua Di Parma Blu Meditteraneo Cipresso
Acqua Di Parma Colonia Assoluta Bath
Acqua Di Parma Colonia Assoluta
Acqua Di Parma Colonia Body Cream
Acqua Di Parma Colonia Body Cream Tube
Adidas  Deep Energy
Adidas  Dynamic Pulse
Adidas  Fair Play

As you can see these are all variations of Acqua Di Parma Blu Mediterraneo and Adidas products

Is there a way to read the data, letter by letter, then when the next letter does not appear more than say 3 times, return what is before the letter change

Basically, I want to read this list and return only

Acqua Di Parma Blu Meditteraneo
Acqua Di Parma Colonia
Adidas  Deep Energy
Adidas  Dynamic Pulse
Adidas  Fair Play

The whole table is about 70,000 rows all of similar data.

The table consists of row_id, title, category

Possible?

Many thanks

Darren

Do you have access to some scripting language, or does this need to be in "pure" sql? — Jens
– Jens, Commented Oct 25, 2011 at 10:55
Also, why does your result consist of all three "Adidas" lines instead of just one? — Jens
– Jens, Commented Oct 25, 2011 at 10:59
@Jens PHP - and you're right - I need to rethink how to ask the question, not explaining it very well — StudioTime
– StudioTime, Commented Oct 25, 2011 at 11:25

Trevor North · Accepted Answer · 2011-10-25 14:49:44Z

OK - this isnt pretty and not sure it's completely right but it's the closest i could get.

I created a separate table containing each group of substrings like this

create table subs as
select title, 
       substring_index(title, ' ',1) one,
       substring_index(title, ' ',2) two,
       substring_index(title, ' ',3) three,
       substring_index(title, ' ',4) four,
       substring_index(title, ' ',5) five,
       substring_index(title, ' ',6) six,
       substring_index(title, ' ',7) seven
  from title;

and then created a query to check if a group by of one column was greater than 1 (ie not unique) and the group by of then next column was = 1 (i.e. unique) and that the previous column was a substring of the next, then just unioned together the result of each pair of columns and finally did a select distinct across the whole lot

    select distinct brand from (
    select * from
    (select one brand, count(*) bcount
    from subs
    group by one) one,
    (select two prod, count(*) pcount
    from subs
    group by two) two
    where bcount > 1 
    and pcount=1
    and locate(one.brand, two.prod)>0
    union all
    select * from
    (select two brand, count(*) bcount
    from subs
    group by two) two,
    (select three prod, count(*) pcount
    from subs
    group by three) three
    where two.bcount > 1 
    and three.pcount=1
    and locate(two.brand, three.prod)>0
    union all
    select * from
    (select three brand, count(*) bcount
    from subs
    group by three) three,
    (select four prod, count(*) pcount
    from subs
    group by four) four
    where three.bcount > 1 
    and four.pcount=1
    and locate(three.brand, four.prod)>0
    union all
    select * from
    (select four brand, count(*) bcount
    from subs
    group by four) four,
    (select five prod, count(*) pcount
    from subs
    group by five) five
    where four.bcount > 1 
    and five.pcount=1
    and locate(four.brand, five.prod)>0
    union all
    select * from
    (select five brand, count(*) bcount
    from subs
    group by five) five,
    (select six prod, count(*) pcount
    from subs
    group by six) six
    where five.bcount > 1 
    and six.pcount=1
    and locate(five.brand, six.prod)>0
    union all
    select * from
    (select six brand, count(*) bcount
    from subs
    group by six) six,
    (select seven prod, count(*) pcount
    from subs
    group by seven) seven
    where six.bcount > 1 
    and seven.pcount=1
    and locate(six.brand, seven.prod)>0) x

which results in the following

enter image description here

But it still has some problems as it shows both Aqua Di Parma Blu and Aqua Di Parma Medit.. in two lines instead of just once so it's not correct.

Guess the only way to remove the duplicates is to repeat again on this new set. Then once you have the distinct set, any products with this as the leading string is that brand. Hope it helps.

Collectives™ on Stack Overflow

SQL extract data after character change MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related