scotrail: assemble_sentence

Enter a sequence of terms separated by commas, like this:

i am sorry, scotrail, from, bath spa, is delayed, due to, bomb

Each term will be treated as a search, and the first, shortest matching clip will be selected.

You can then hit "Play all" to hear all the clips as a sequence.

Custom SQL query returning 8 rows (hide) 

with phrases as (
  select
    key, value
  from
    json_each('["' || replace(:terms, ',', '","') || '"]')
),
matches as (select
  phrases.key,
  phrases.value,
  (
    select File from announcements
    where announcements.Transcription like '%' || trim(phrases.value) || '%'
    order by length(announcements.Transcription)
    limit 1
  ) as File
from
  phrases
),
results as (
  select key, announcements.Transcription, announcements.mp3
  from announcements join matches on announcements.File = matches.File
  order by key
)
select
  'Combined sentence:' as mp3,
  group_concat(Transcription, ' ') as Transcription,
  -1 as key
from results
  union
select
  mp3, Transcription, key
from results
order by key

Query parameters

Edit SQL

This data as json, CSV

mp3Transcriptionkey
Combined sentence: I am sorry to announce that the ScotRail service from Bath Spa is delayed due to A wartime bomb near the railway -1
I am sorry to announce that the 0
ScotRail 1
service from 2
Bath Spa 3
is delayed 4
due to 5
A wartime bomb near the railway 6