MySQL Forums
Forum List  »  Newbie

Re: Pattern matching problem
Posted by: Chad Bourque
Date: September 03, 2010 08:37AM


As promised, here is a stored procedure that will explode those values for you. You may need to tweak it for your tables. This one is based on the following articles table (which you described):

create table Articles
  ID int not null auto_increment primary key,
  `Date` date null,
  Sector varchar(20) null,
  Tickers varchar(255) null,
  Title varchar(20) null,
  Link varchar(20) null,
  Body varchar(20) null

insert into Articles (Tickers) values
  ('A, BC, DEF, GHIJ, KLM'),
  ('NO, PQRS'),

That would represent your current table. Then, a new tickers table would need to be created:

create table Tickers
  ID int not null,
  Ticker varchar(10) not null,
  constraint pk_Tickers
    primary key (ID, Ticker),
  constraint fk_Tickers_Articles
    foreign key (ID)
    references Articles (ID)
    on delete cascade
    on update cascade

Here's the stored procedure that would do the work:

delimiter $$

drop procedure if exists explode $$
create procedure explode
  declare _done int default 0;
  declare _id int;
  declare _tickers varchar(255);
  declare _word varchar(10);
  declare _cursor cursor for select ID, Tickers from Articles where Tickers is not null;
  declare continue handler for not found set _done = 1;

  open _cursor;

  fetch _cursor into _id, _tickers;
  while (_done = 0) do
    while (length(_tickers) > 0) do
      set _word = trim(substring_index(_tickers, ',', 1));
      set _tickers = substring(_tickers, length(_word) + 2);

      if (length(_word) > 0) then
        insert ignore into Tickers (ID, ticker) values (_id, _word);
      end if;
    end while;

    fetch _cursor into _id, _tickers;
  end while;

  close _cursor;
end $$

delimiter ;

You would call it like this:

call explode();

You can look at the new Tickers table to see the data:

mysql> select * from Tickers;
| ID | Ticker |
|  1 | A      |
|  1 | BC     |
|  1 | DEF    |
|  1 | GHIJ   |
|  1 | KLM    |
|  2 | NO     |
|  2 | PQRS   |
|  3 | TUV    |
8 rows in set (0.00 sec)

Now, you can drop the old Tickers column from the Articles table since it is no longer needed:

alter table Articles
  drop Tickers;

You could also drop the explode procedure since this is really a one time deal (and it will not work after you drop the Tickets column from the Articles table):

drop procedure if exists explode;

And there you have it.


Options: ReplyQuote

Written By
September 02, 2010 01:11PM
September 02, 2010 02:33PM
September 02, 2010 02:46PM
September 02, 2010 03:10PM
September 02, 2010 04:24PM
September 02, 2010 08:55PM
Re: Pattern matching problem
September 03, 2010 08:37AM
September 07, 2010 03:25PM
September 07, 2010 04:39PM
September 07, 2010 06:13PM
September 07, 2010 07:08PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.