Creating a sort where certain leading chars are ignored

[jda]

Hi Ruslan,

I create a temporary field so that I can sort names by. I have a method that lets me ignore certain leading "names", like

  van
  von
  de

etc.

So

  de Long

would sort by

  Long

I'd like to be able to specify characters to ignore, too, so if the user puts the name in brackets

  [Ruslan]

it would sort by

  Ruslan (not [Ruslan]).

Here's what I'm doing (it's looking for the word to be ignored at the beginning of the names, and removing it if found):

tempField = myDatabase.myReferences.createVarCharField("authorEditorSortMethod", 1000, EVFlag.fTemporary, s)

where

s = "CCASE WHEN LOCATE(UPPER('von '),UPPER(coalesce(authors,editors)))=1 THEN RIGHT(coalesce(authors,editors), LENGTH(coalesce(authors,editors)) - LENGTH('von ')) WHEN LOCATE(UPPER('[ '),UPPER(coalesce(authors,editors)))=1 THEN RIGHT(coalesce(authors,editors), LENGTH(coalesce(authors,editors)) - LENGTH('[ ')) ELSE coalesce(authors,editors) END"

It works great for words containing letters, but not punctuation. So I can't get t his to work with "names" beginning with [. That is, it does not work with this construction

CASE WHEN LOCATE(UPPER('[ '),UPPER(coalesce(authors,editors)))=1 THEN RIGHT(coalesce(authors,editors), LENGTH(coalesce(authors,editors)) - LENGTH('[ ')) ELSE coalesce(authors,editors) END"

Any ideas? What would I do to make this identify "words" beginning with punctuation?

And also, how can I make this case *sensitive*?

Thanks,

Jon