Re: Pattern matching problem

[Chad Bourque]

Ben,

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'),
  ('TUV'),
  (''),
  (null);

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
(
)
begin
  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.

HTH,
Chad