Creating a sort where certain leading chars are ignored

[Ivan Smahin]

Hello jda,

Saturday, June 26, 2010, 9:35:59 PM, you wrote:

>> 
>> 
>> To ignore case sensitive:
>> select
>>    trim(regex_replace( f1, '(?i)\\[|\\]|van|der|von', '\\'))
>> from
>>    t1
>> 
>> 
> Thanks, Ivan. But the records are already in a cursor (they are an arbitrary subset of a table).

Don't get the point.
I propose to change your method completely.

trim(regex_replace( f1, '(?i)\\[|\\]|van|der|von', '\\'))

instead of all that "cases".
BTW, don't you think it is too much to call coalesce, upper, length
few times per each record?

> I tried removing the UPPER's from my method, like this

> CASE
> WHEN LOCATE('Von'), coalesce(authors,editors))=1 THEN
> RIGHT(coalesce(authors,editors),LENGTH(coalesce(authors,editors)) - LENGTH('Von'))
> ELSE
> coalesce(authors,editors) END

> But it still didn't seem to be case sensitive. Can LOCATE be case
> sensitive? Or is there another function I can substitute that is?

So the only thing you are worrying about is removing "upper" function?

I'm  trying  to  reproduce  anything  like  "it would not work for punctuation"
but I don't not see any problems - everything works as expected!



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