Creating a sort where certain leading chars are ignored
jda
jda at his.com
Sun Jun 27 08:23:38 CDT 2010
>>>
>>>
>> 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>
More information about the Valentina
mailing list