Reply to thread

Message: [QUOTE="Jamie Collins, post: 10723540"] Typically, the next question is, "How do I find the second to last word?" :) If you employ the standard trick of a Sequence table of unique integers, you can use it to parse out delimited text in a column, including a space-separated text. This example uses Northwind: SELECT T1.CustomerID, S1.seq AS pos, MID(T1.CompanyName, S1.seq, MIN(S2.seq) - S1.seq - 1) AS word FROM Customers AS T1, Sequence AS S1, Sequence AS S2 WHERE MID(' ' & T1.CompanyName & ' ', S1.seq, 1) = ' ' AND MID(' ' & T1.CompanyName & ' ', S2.seq, 1) = ' ' AND S1.seq BETWEEN 1 AND 40 AND S2.seq BETWEEN 1 AND 40 AND S1.seq < S2.seq GROUP BY T1.CustomerID, T1.CompanyName, S1.seq HAVING LEN(MID(T1.CompanyName, S1.seq, MIN(S2.seq) - S1.seq - 1)) > 0 ORDER BY T1.CustomerID, S1.seq; Obviously, finding the last word is a simply matter of finding the row with MAX(pos). Jamie. -- [/QUOTE]