Spectrum Computing

Code: Select all

create table libraries (
  title varchar(300) collate utf8_unicode_ci not null primary key,
  library_title varchar(300) collate utf8_unicode_ci not null
);

insert into libraries(title, library_title) (select title, '' from entries group by title collate utf8_unicode_ci);
insert into libraries(title, library_title) (select title, '' from aliases where title collate utf8_unicode_ci not in (select title from libraries) group by title collate utf8_unicode_ci);

update libraries s
inner join prefixes p on s.title like concat(p.text,'%')
left join prefixexempts x on s.title like concat(x.text,'%')
set s.library_title = trim(concat(substr(s.title,length(p.text)+1),', ',left(s.title, length(p.text))))
where x.text is null;

Code: Select all

create table libraries (
  title varchar(300) collate utf8_unicode_ci not null primary key,
  library_title varchar(300) collate utf8_unicode_ci not null
);

insert into libraries(title, library_title) (select title, '' from entries group by title collate utf8_unicode_ci);
insert into libraries(title, library_title) (select title, '' from aliases where title collate utf8_unicode_ci not in (select title from libraries) group by title collate utf8_unicode_ci);

update libraries set library_title = concat(substr(title,4),', 3D') where title like '3D %';
update libraries set library_title = concat(substr(title,3),', A') where title like 'A %' and title not like 'A B C%' and title not like 'A I AA%' and title not like 'A La Carte%' and title not like 'A Maci %' and title not like 'A to Z%' and title not like 'A to snad%';
update libraries set library_title = concat(substr(title,4),', An') where title like 'An %';
update libraries set library_title = concat(substr(title,4),', As') where title like 'As %';
update libraries set library_title = concat(substr(title,5),', Das') where title like 'Das %';
update libraries set library_title = concat(substr(title,4),', De') where title like 'De %';
update libraries set library_title = concat(substr(title,5),', Der') where title like 'Der %';
update libraries set library_title = concat(substr(title,5),', Des') where title like 'Des %';
update libraries set library_title = concat(substr(title,5),', Die') where title like 'Die %' and title not like 'Die Alien%' and title not like 'Die Hard%';
update libraries set library_title = concat(substr(title,4),', El') where title like 'El %';
update libraries set library_title = concat(substr(title,4),', Em') where title like 'Em %';
update libraries set library_title = concat(substr(title,5),', Het') where title like 'Het %';
update libraries set library_title = concat(substr(title,4),', Il') where title like 'Il %';
update libraries set library_title = trim(concat(substr(title,3),', L''')) where title like 'L''%';
update libraries set library_title = concat(substr(title,4),', La') where title like 'La %' and title not like 'LA Drugs%';
update libraries set library_title = concat(substr(title,5),', Las') where title like 'Las %' and title not like 'Las Vegas%';
update libraries set library_title = concat(substr(title,4),', Le') where title like 'Le %' and title not like 'Le Mans%';
update libraries set library_title = concat(substr(title,5),', Les') where title like 'Les %';
update libraries set library_title = concat(substr(title,4),', Lo') where title like 'Lo %' and title not like 'Lo Profile%';
update libraries set library_title = concat(substr(title,5),', Los') where title like 'Los %' and title not like 'Los Angeles%';
update libraries set library_title = concat(substr(title,3),', O') where title like 'O %';
update libraries set library_title = concat(substr(title,4),', Os') where title like 'Os %' and title not like 'OS DRAW%';
update libraries set library_title = concat(substr(title,5),', The') where title like 'The %';
update libraries set library_title = concat(substr(title,4),', Un') where title like 'Un %' and title not like 'Un Dos%';
update libraries set library_title = concat(substr(title,5),', Una') where title like 'Una %';
update libraries set library_title = concat(substr(title,5),', Une') where title like 'Une %';

Code: Select all

create table libraries (
  title varchar(300),
  library_title varchar(300)
);

insert into libraries
select title, '' from entries group by title;

insert into libraries
select title, '' from aliases where title not in (select title from libraries) group by title;

update libraries as s
set library_title = trim(substr(s.title, length(p.text)+1) || ', ' || substr(s.title, 1, length(p.text)))
from prefixes as p  
where s.title like p.text || '%';

update libraries as s    
set library_title = title
from prefixexempts as p  
where s.title like p.text || '%';

Code: Select all

Parse error near line 565472: near "CHARACTER": syntax error
  TABLE IF NOT EXISTS prefixes (   text varchar(10) CHARACTER SET utf8 COLLATE u
                                      error here ---^
Parse error near line 565478: no such table: prefixes
Parse error near line 565508: near "CHARACTER": syntax error
   IF NOT EXISTS prefixexempts (   text varchar(50) CHARACTER SET utf8 COLLATE u
                                      error here ---^
Parse error near line 565514: no such table: prefixexempts