CODEWITHSUNDEEP
mysqlsql
Faramos
Faramos

Reputation: 21

MySQL - How to recieve table of substrings from string

I got table in my DB with pairs EVENT_ID - ATTENDANTS varchar(300) containing data in form of string concatenated from repetitive attributes and unique user names. Delimiter between attribute and name is ":" and every collection of "attribute:user" ends with ";" There are spaces in some attributes names. Amount of pairs in every line is unknown.

"attributeC:user_name1;attributeA:user_name3;attributeA:user_name4;attributeA:user_name10;attributeB:user_name42;"

Then i got table with pairs USER_ID - USER_NAME and destination table with fields USER_ID - EVENT_ID - ATTRIBUTE.

What is the best way to extract users and attributes and insert them into destination table?

I would love to do this in SQL (MySQL), in case of using functions etc, making them temporary, script will be run only one time.

If it would be to much complicated, I am open to use PHP with OOP mysqli functions. (It will be run in set of other $mysqli->query() commands)

I found, that MySQL functions SUBSTRING(), LOCATE(), LENGTH() could come handy.

I am able to do this in some laborious way via PHP

  1. make array based on SELECT(USER_ID - USER_NAME)
  2. make SELECT(EVENT_ID - ATTENDANTS) and for every row call explode(), get attribute, EVENT_ID, USER_ID (from array above, instead of SQL querying for every username in every string for every event)
  3. paralelly insert data to destination table with mysqli prepared statement

But I think (hope?) there is some better way. What do you think?

Upvotes: 2

Views: 87

Answers (2)

Faramos
Faramos

Reputation: 21

After some research, I solved it and it´s not as dumb way as it may look like.

Code first:

select OUTPUT.ID, OUTPUT.name, OUTPUT.attribute from
(
select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 1), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 1), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 2), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 2), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 3), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 3), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 4), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';',4), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 5), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 5), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 6), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 6), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 7), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 7), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 8), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 8), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 9), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 9), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 10), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 10), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 11), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 11), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 12), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 12), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 13), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 13), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 14), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 14), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 15), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 15), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 16), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 16), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 17), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 17), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 18), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 18), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 19), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 19), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 20), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 20), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 21), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 21), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 22), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 22), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 23), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 23), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 24), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 24), ';', -1), ':', -1) as name
from AKCE

union

select
  AKCE.ID,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 25), ';', -1), ':', 1) as attribute,
  substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 25), ';', -1), ':', -1) as name
from AKCE

order by ID
) AS OUTPUT WHERE OUTPUT.name!='' AND OUTPUT.attribute!=''

As you can see, it is long, full of redundant code etc.

When you use substring_index() with position greater than number of deimiters inside, it will return empty field. And because of unions, those empty fields are returned just one time per original table row (AKCE.ID).

Those empty rows are filtered with WHERE ... != ' '

It certainly isn´t the right way, but on 150 rows with maximum 23 pairs it works pretty good nad fast (only 10 times slower than basic select on that table)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can do this rather painfully in MySQL. Here is an example for getting the first three such strings:

select t.col, n.n,
       substring_index(substring_index(t.col, ';', n.n), ';', -1) as pair,
       substring_index(substring_index(substring_index(t.col, ';', n.n), ';', -1), ':', 1) as name,
       substring_index(substring_index(substring_index(t.col, ';', n.n), ';', -1), ':', -1) as value
from t join
     (select 1 as n union all select 2 union all select 3
     ) n
     on length(t.col) - length(replace(t.col, ';', '') + 1 <= n.n;

You just need to add the additional numbers to the n subquery to get more pairs.

Upvotes: 1

Related Questions