Creating a sort where certain leading chars are ignored

[Ivan Smahin]

Hello Ivan,

Saturday, June 26, 2010, 8:26:52 PM, you wrote:

> Hello jda,

> Friday, June 25, 2010, 2:42:49 AM, you wrote:

>> 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*?

> it could be as simple as

> select
>    trim(regex_replace( f1, '\\[|\\]|van|der|von', '\\'))
> from
>    t1


To ignore case sensitive:
 select
    trim(regex_replace( f1, '(?i)\\[|\\]|van|der|von', '\\'))
 from
    t1




>> _______________________________________________
>> Valentina mailing list
>> Valentina at lists.macserve.net
>> http://lists.macserve.net/mailman/listinfo/valentina





-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com