<html><head></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><div><blockquote type="cite"><div><blockquote type="cite"><blockquote type="cite"><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><br></blockquote></blockquote><blockquote type="cite">Thanks, Ivan. But the records are already in a cursor (they are an arbitrary subset of a table).<br></blockquote><br>Don't get the point.<br>I propose to change your method completely.<br><br>trim(regex_replace( f1, '(?i)<a href="smb://[|//]|van|der|von',">\\[|\\]|van|der|von',</a> '\\'))<br><br>instead of all that "cases".<br></div></blockquote><div><br></div>Hi Ivan,</div><div><br></div><div>I'm afraid I'm not that knowledgable about Regex.</div><div><br></div><div>For example, you may be using "regex_replace" as pseudocode, because I see no mention of that in any Valentina docs or online.</div><div><br></div><div><br><blockquote type="cite"><div>BTW, don't you think it is too much to call coalesce, upper, length<br>few times per each record?<br></div></blockquote><div><br></div>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.</div><div><br><blockquote type="cite"><div><br><font class="Apple-style-span" color="#006312"><br></font><blockquote type="cite">CASE<br></blockquote><blockquote type="cite">WHEN LOCATE('Von'), coalesce(authors,editors))=1 THEN<br></blockquote><blockquote type="cite">RIGHT(coalesce(authors,editors),LENGTH(coalesce(authors,editors)) - LENGTH('Von'))<br></blockquote><blockquote type="cite">ELSE<br></blockquote><blockquote type="cite">coalesce(authors,editors) END<br></blockquote><br><blockquote type="cite">But it still didn't seem to be case sensitive. Can LOCATE be case<br></blockquote><blockquote type="cite">sensitive? Or is there another function I can substitute that is?<br></blockquote><br>So the only thing you are worrying about is removing "upper" function?<br></div></blockquote><div><br></div>Yes. I changed the method to this:</div><div><br></div><div>CASE WHEN LOCATE('Von ',coalesce(authors,editors))=1 THEN RIGHT(coalesce(authors,editors), LENGTH(coalesce(authors,editors)) - LENGTH('Von ')) ELSE coalesce(authors,editors) END</div><div><br></div><div>And in the output of names, both</div><div><br></div><div> von Simms</div><div><br></div><div>and</div><div><br></div><div> Von Simms</div><div><br></div><div>sorted with the "s"'s, e.g.</div><div><br></div><div> Salle</div><div> von Simms</div><div> Von Simms</div><div> Smith</div><div><br></div><div>According to method above I would expect only</div><div><br></div><div> Von Simms</div><div><br></div><div>to sort there.</div><div><br></div><div><br></div><div><blockquote type="cite"><div><br>I'm trying to reproduce anything like "it would not work for punctuation"<br>but I don't not see any problems - everything works as expected!<br><br><br></div></blockquote></div><br><div>Aha, you're right. This was a mistake in my code, it works for me, too.</div><div><br></div><div>So the only thing I'm not getting to work is the case sensitive LOCATE. I'll keep plugging away at it.</div><div><br></div><div>Thanks for your patience.</div><div><br></div><div>Jon</div></body></html>