Longwinded custom SQL statement to add Metadata to MythVideo

The following SQL statement allows me to select and format  the entires into the MythVideo Metadata table for recordings made with my Panasonic camera. Posted here just for future reference.

SELECT substr(`filename`,32,10),
CONCAT_WS(':',substr(`filename`,43,2),concat(substr(`filename`,45,2),' Uhr'))
FROM `videometadata`
WHERE `filename` LIKE '%MTS%'
AND `filename` REGEXP '[0-9]{4}[-][0-9]{2}[-][0-9]{2}[-]'
AND NOT(`filename` REGEXP '[0-9]{8}')
AND NOT(`filename` LIKE '%720%')
AND NOT(`filename` LIKE '%Unsere Videos%')
LIMIT 0 , 100

then I only need to convert it to an Update statement like

UPDATE mythconverg.videometadata

to comit it to the database.

Similarly, to add the Season and Episode numbers (S##E##) to the syndicatedepisodenumber field using this command:

UPDATE mythconverg.recordedprogram
SET `syndicatedepisodenumber` = concat(concat(concat(convert(substr(`subtitle`,2,2),SIGNED),'.'),convert(substr(`subtitle`,5,2),SIGNED)),'.0/0')
WHERE `subtitle` REGEXP '^[S][0-9]{2}[E][0-9]{2}'

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.