Creating a sort where certain leading chars are ignored

[jda]

>>> 
>>> 
>> 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".

Hi Ivan,

I'm afraid I'm not that knowledgable about Regex.

For example, you may be using "regex_replace" as pseudocode, because I see no mention of that in any Valentina docs or online.


> BTW, don't you think it is too much to call coalesce, upper, length
> few times per each record?

No, it does what I need, and it's instantaneous (at least as perceived by the user). This method is usually applied to only 30-100 records.

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

Yes. I changed the method to 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

And in the output of names, both

   von Simms

and

   Von Simms

sorted with the "s"'s, e.g.

   Salle
   von Simms
   Von Simms
   Smith

According to method above I would expect only

   Von Simms

to sort there.


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

Aha, you're right. This was a mistake in my code, it works for me, too.

So the only thing I'm not getting to work is the case sensitive LOCATE. I'll keep plugging away at it.

Thanks for your patience.

Jon
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macserve.net/pipermail/valentina/attachments/20100627/27402fc3/attachment-0001.html>