Sorting compound names

[jda]

>
>
>You could use a virtual field (with a method) that do the replace/split.
>Then you do the sort on that field.
>

Hm, interesting. But I can't imagine how I would implement the sql 
management of the text -- it would require compound loops to deal 
with all occurrences.

I've come up with this idea as an approach:

The field to sort contains a list of CR-separated names:

van Laaken, D
Smith, TR
Van Meer, FG

The list of "forbidden" words includes "van"

1. Build a case-insensitive tmp table from the original cursor with 
the name field and unique id field
2. RegEx find records where a new line begins with "van "
3. Put the names field of each into an RB string array.
4. Do a RegEx search/replace for new lines beginning with "van " and 
replace with ""
5. Update the tmp table from the cursor
6. Create a cursor of everything in the tmp table ORDERED BY name
7. Dispose of the tmp table
8. Fetch each unique id from the cursor into a string and make a 
*new* cursor from the original table:

select * from myRecords where uniqueID=6 or uniqueID=343 or uniqueID=3...

That new cursor now has the records sorted without regard to the 
forbidden prefixes.

What do you think?

Jon