Back to SQL matters, this works but it makes me feel dirty. I'm parsing opus numbers and the like from the titles, and the current album includes two different numbering schemes. So I'm trying to construct three semicolon-separated fields for eventual splitting and correlating with one another: work, worknumber, and worksortorder. The last is a concatenation of the numeric portions of the other two, each of those numerals left-padded with zeros to take the general shape like '0000-0000'. However, if it is all zeroes, it should be nulled out. For example, a track with "Op. 4 No. 1, RV 383" will have "Op. 4; RV 383", "No. 1;", and "0004-0001; 0383-0000" in those three fields.
Yes, it gets more complicated than this. Some RV numbers on this album end in "a" or "b", and the next album has Fanna numbers with Roman numerals and slashes. Heavens, this is madness, but one thing at a time.
Like I said, this works, but I had to resort to a hairy eval() that makes me think there must be a better solution. Is there?
Base regex:
DROP TABLE IF EXISTS regexes;
CREATE TABLE regexes (
name TEXT UNIQUE PRIMARY KEY,
expression TEXT
);
INSERT INTO regexes
VALUES (
'work' -- opus and number
, '(?i)('
|| '?(DEFINE)'
|| '(?<titlecase>(?:\b(?:op)(?=[\s\d\.])\.?))'
|| '(?<uppercase>(?:\b(?:bb|bwv|kv?|rv)(?=[\s\d\.])\.?))'
|| '(?<mixedcase>(?:\b(?:WoO|BuxWV)(?=[\s\d\.])\.?))'
|| '(?<posth>\s*(?:\bposth?(?=[\s\d\.])\.?)?)'
|| '(?<sp>(?:\s*))'
|| '(?<num>(?:\bn(?:o|º)(?=[\s\d\.])\.?))'
|| ')'
|| '(?:'
|| '(?:'
|| '(?<titlecase>(?&titlecase))'
|| '|(?<uppercase>(?&uppercase))'
|| '|(?<mixedcase>(?&mixedcase))'
|| ')'
|| '(?<posth>(?&posth))'
|| '(?<workspace>(?&sp))'
|| '(?<worknum>(\d+)?)'
|| '(?:'
|| '\s*(?<numbertext>(?&num))'
|| '\s*(?<numbernum>(\d+))'
|| ')?'
|| ')'
)
SELECT queries referring to regex table
DROP VIEW IF EXISTS view_workfromtitle;
CREATE VIEW view_workfromtitle AS
WITH reg AS (
SELECT expression ex
FROM regexes
WHERE name = 'work'
)
SELECT regexp_replace(
reg.ex
, '\u$+{titlecase}\U$+{uppercase}\E$+{mixedcase}'
|| '$+{posth}$+{workspace}$+{worknum}; '
, title
, 1
)
AS work
, replace(regexp_replace(
reg.ex
, '$+{numbertext} $+{numbernum}; '
, title
, 1
)
, 'º', 'o'
)
AS worknumber
, regexp_replace(reg.ex, 'nullif\(padl\(''$+{worknum}'',4,''0''\)||''-''||padl\(''$+{numbernum}'',4,''0''\),''0000-0000''\)||''; ''||', title, 1)
AS worksortorder
, title
FROM Playlist_Updatable, reg
WHERE playlist_index = active_playlist()
AND item_is_selected;
SELECT substr(work, 1, length(work) - 2)
AS work
, substr(worknumber, 1, length(worknumber) - 2)
AS worknumber
, eval('SELECT '||substr(worksortorder, 1, length(worksortorder) - 8))
AS worksortorder
, title
FROM view_workfromtitle
Sample output
work worknumber worksortorder title
Op. 4; RV 383 No 1; 0004-0001; 0383-0000 Concierto en Si bemol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 1, RV 383a, I. Allegro
Op. 4; RV 383 No 1; 0004-0001; 0383-0000 Concierto en Si bemol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 1, RV 383a, II. Largo
Op. 4; RV 383 No 1; 0004-0001; 0383-0000 Concierto en Si bemol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 1, RV 383a, III. Allegro
Op. 4; RV 279 No 2; 0004-0002; 0279-0000 Concierto en Mi menor para Violín, Cuerdas y Continuo, Op. 4 Nº 2, RV 279, I. Allegro
Op. 4; RV 279 No 2; 0004-0002; 0279-0000 Concierto en Mi menor para Violín, Cuerdas y Continuo, Op. 4 Nº 2, RV 279, II. Largo
Op. 4; RV 279 No 2; 0004-0002; 0279-0000 Concierto en Mi menor para Violín, Cuerdas y Continuo, Op. 4 Nº 2, RV 279, III. Allegro
Op. 4; RV 301 No 3; 0004-0003; 0301-0000 Concierto en Sol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 3, RV 301, I. Allegro
Op. 4; RV 301 No 3; 0004-0003; 0301-0000 Concierto en Sol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 3, RV 301, II. Largo
Op. 4; RV 301 No 3; 0004-0003; 0301-0000 Concierto en Sol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 3, RV 301, III. Allegro assai
Op. 4; RV 357 No 4; 0004-0004; 0357-0000 Concierto en La menor para Violín, Cuerdas y Continuo, Op. 4 Nº 4, RV 357, I. Allegro
Op. 4; RV 357 No 4; 0004-0004; 0357-0000 Concierto en La menor para Violín, Cuerdas y Continuo, Op. 4 Nº 4, RV 357, II. Grave
Op. 4; RV 357 No 4; 0004-0004; 0357-0000 Concierto en La menor para Violín, Cuerdas y Continuo, Op. 4 Nº 4, RV 357, III. Allegro
Op. 4; RV 347 No 5; 0004-0005; 0347-0000 Concierto en La Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 5, RV 347, I. Allegro
Op. 4; RV 347 No 5; 0004-0005; 0347-0000 Concierto en La Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 5, RV 347, II. Largo
Op. 4; RV 347 No 5; 0004-0005; 0347-0000 Concierto en La Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 5, RV 347, III. Allegro
Op. 4; RV 316 No 6; 0004-0006; 0316-0000 Concierto en Sol menor para Violín, Cuerdas y Continuo, Op. 4 Nº 6, RV 316a, I. Allegro
Op. 4; RV 316 No 6; 0004-0006; 0316-0000 Concierto en Sol menor para Violín, Cuerdas y Continuo, Op. 4 Nº 6, RV 316a, II. Largo
Op. 4; RV 316 No 6; 0004-0006; 0316-0000 Concierto en Sol menor para Violín, Cuerdas y Continuo, Op. 4 Nº 6, RV 316a, III. Allegro
Op. 4; RV 185 No 7; 0004-0007; 0185-0000 Concierto en Do Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 7, RV 185, I. Largo - Allegro
Op. 4; RV 185 No 7; 0004-0007; 0185-0000 Concierto en Do Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 7, RV 185, II. Largo
Op. 4; RV 185 No 7; 0004-0007; 0185-0000 Concierto en Do Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 7, RV 185, III. Allegro
Op. 4; RV 249 No 8; 0004-0008; 0249-0000 Concierto en Do Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 8, RV 249, I. Allegro - Adagio - Presto - Adagio
Op. 4; RV 249 No 8; 0004-0008; 0249-0000 Concierto en Re menor para Violín, Cuerdas y Continuo, Op. 4 Nº 8, RV 249, II. Adagio
Op. 4; RV 249 No 8; 0004-0008; 0249-0000 Concierto en Re menor para Violín, Cuerdas y Continuo, Op. 4 Nº 8, RV 249, III. Allegro
Op. 4; RV 284 No 9; 0004-0009; 0284-0000 Concierto en Fa Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 9, RV 284, I. Allegro
Op. 4; RV 284 No 9; 0004-0009; 0284-0000 Concierto en Fa Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 9, RV 284, II. Largo
Op. 4; RV 284 No 9; 0004-0009; 0284-0000 Concierto en Fa Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 9, RV 284, III. Allegro
Op. 4; RV 196 No 10; 0004-0010; 0196-0000 Concierto en Do menor para Violín, Cuerdas y Continuo, Op. 4 Nº 10, RV 196, I. Spiritoso
Op. 4; RV 196 No 10; 0004-0010; 0196-0000 Concierto en Do menor para Violín, Cuerdas y Continuo, Op. 4 Nº 10, RV 196, II. Adagio
Op. 4; RV 196 No 10; 0004-0010; 0196-0000 Concierto en Do menor para Violín, Cuerdas y Continuo, Op. 4 Nº 10, RV 196, III. Allegro
Op. 4; RV 204 No 11; 0004-0011; 0204-0000 Concierto en Re Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 11, RV 204, I. Allegro
Op. 4; RV 204 No 11; 0004-0011; 0204-0000 Concierto en Re Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 11, RV 204, II. Largo
Op. 4; RV 204 No 11; 0004-0011; 0204-0000 Concierto en Re Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 11, RV 204, III. Allegro assai
Op. 4; RV 298 No 12; 0004-0012; 0298-0000 Concierto en Sol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 12, RV 298, I. Spiritoso e non presto
Op. 4; RV 298 No 12; 0004-0012; 0298-0000 Concierto en Sol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 12, RV 298, II. Largo
Op. 4; RV 298 No 12; 0004-0012; 0298-0000 Concierto en Sol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 12, RV 298, III. Allegro