The attached uses method #4 in The Cross Join Collection.
If all sub-strings are known, then method #5 is a better choice.
This is the cross join used assuming space is the item separator:
SELECT d.[ItemName] , l.[Pos] , mid$(d.[ItemName], l.Pos, instr(l.Pos, d.[ItemName] + ' ', ' ') - l.Pos) as [Item] FROM [data$] d, [lookup$] l WHERE l.Pos <= len(d.[ItemName]) AND mid$(' ' + d.[ItemName], l.Pos, 1) = ' '
Attached was opened with the Legacy Connector:
Ps. This will be easy to do in Tableau 9.
See more about the "vertical" split function from 1:10:59 to 1:12:04 in
Thanks much for this. Much appreciated.
I am in a bit of a technical snag though now. I modified the data and lookup sheets with my data, and used the same custom SQL query to do the extraction, but am getting a DB error and am unable to view the output while using a live connection or an extract. Any tweak you might suggest?
Thanks for this, pal. Thing is, I have a huge list of pattern strings, so this method wouldn't be viable for my current exercise.
I might find out what the issue is if you share spreadsheet, query and db error.