Creating a sort where certain leading chars are ignored

[Ivan Smahin]

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



> _______________________________________________
> 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