I figured this out in the end, something like –
SELECT SUBSTRING(a.name, 1, LEN(b.NamePrefix)) AS Expr1, COUNT(a.name) AS Expr2, b.NamePrefix
FROM Customers a INNER JOIN
NamePrefixes b ON b.NamePrefix = SUBSTRING(a.name, 1, LEN(b.NamePrefix))
Gives me a count of names beginning with the matches in the nameprefixes table.
So if nameprefixes table has fred in it and names contains Fred, Frederick, Freddy, I get Fred and 3 as the count.
Thanks for showing an interest anyway