SELECT str
FROM strings
WHERE SUBSTRING(str
FROM LEAST(NULLIF(POSITION('0' IN str),0)
,NULLIF(POSITION('1' IN str),0)
,NULLIF(POSITION('2' IN str),0)
,NULLIF(POSITION('3' IN str),0)
,NULLIF(POSITION('4' IN str),0)
,NULLIF(POSITION('5' IN str),0)
,NULLIF(POSITION('6' IN str),0)
,NULLIF(POSITION('7' IN str),0)
,NULLIF(POSITION('8' IN str),0)
,NULLIF(POSITION('9' IN str),0))
FOR 1 ) = '1'
I’m working on a bus timetable hack. So when someone is searching for the no 4 bus, they won’t be wanting results for the 44, 48, 84 routes. However some buses have a leading character: C4, H4 etc, So I want those listed as well.
Thanks for the responses, but neither of those worked how I wanted. I have been able to store the search along with the results, so a repeat search gets the same results.
You’re right, my last solution is just your OP solution split in 3. Since you want the busline with optionally the non numeric pre- and suffixes, regex is the way to go.