Creating a sort where certain leading chars are ignored
jda
jda at his.com
Thu Jun 24 18:42:49 CDT 2010
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
More information about the Valentina
mailing list