>Thanks, Mike. There isn't, then, any way of avoiding the $ I take it...

There are tons of ways to avoid the $ substring search, but that takes time and time is $. What Al said jogged my memory.

If you have an
index on yourfield tag xyours
and set exact is off, you can
select (your minimum required fields) from yourtable where yourfield='mca' or "mca"$yourfield

It will very quickly find the ones starting with mca - and only do the substring search on the rest.

Not tested, but If you really want fast substring searches you could parse every letter, then every pair of letters, then every triplet when the user saves changes. Add only new substrings to a "dictionary" table. Add entries to another table for each new collection of letters showing the id of the collection and the record number of yourfield.

If you have a table with yourfield="test" in record 1, the dictionary will have
dctpk, substring
1 "t"
2 "e"
3 "s"
4 "te"
5 "es"
6 "st"
7 "tes"
8 "est"
9 "test"

the substring table will have
sstpk, recordid, dctfk
1, 1, 1
2, 1, 2
3, 1, 3
4, 1, 4
5, 1, 5
6, 1, 6
7, 1, 7
8, 1, 8

select * from yourtable ;
inner join substring on yourtable.recordid = substring.recordid ;
inner join dictionary on dictionary.dctpk = substring.dctfk ;
where dictionary.substring = "es"

Given the indexes on the primary and foreign keys and the dictionary.substring, that should be fast enough.