You’re right that http://calcite.apache.org/docs/reference.html#character-string-operators-and-functions <http://calcite.apache.org/docs/reference.html#character-string-operators-and-functions> needs fixing.
I think we’re doing the right thing in implementing the SQL standard TRIM function according to the spec. Maybe those other systems are not trying to implement the SQL standard function. Note that we also have LTRIM and RTRIM (to be compatible with Oracle; possibly you need fun=oracle in the JDBC URL) and these may do what you want. Julian > On Dec 7, 2017, at 2:40 AM, 于仲(重籽) <[email protected]> wrote: > > Thanks Julian. There are several sources that support the case of multi-char > SC, including Sparks, Microsoft, as well as Calcite's own reference page. But > I guess we only need to worry about the single-char case. > ------------------------------------------------------------------ > 发件人：Julian Hyde <[email protected]> > 发送时间：2017年12月7日(星期四) 18:26 > 收件人：dev <[email protected]> > 主 题：Re: question about trim() function > > I take it you mean > > trim(‘ab’ from ‘abcba') > > (Double-quotes delimit identifiers, not character literals.) > > SQL standard says: > > 6.30.11 If <trim function> is specified, then: > (a) Let S be the value of the <trim source>. > (b) If <trim character> is specified, then let SC be the > value of <trim character>; otherwise, let SC be <space>. > (c) If at least one of S and SC is the null value, then the result of > the <trim function> is the null value. If the length in characters of > SC is not 1 (one), then an exception condition is raised: > data exception — trim error. > > <trim function> ::= > TRIM <left paren> <trim operands> <right paren> > > <trim operands> ::= > [ [ <trim specification> ] [ <trim character> ] FROM ] <trim source> > > <trim source> ::= > <character value expression> > > <trim specification> ::= > LEADING > | TRAILING > | BOTH > > In your example, Calclte should give an error because ‘ab’ does not have > length 1. > > You’ll see a comment to that effect in SqlOperatorBaseTest.testTrimFunc (but > sadly that part of the test is disabled). > > Julian > > > On Dec 7, 2017, at 1:59 AM, 于仲(重籽) <[email protected]> wrote: > > > > It seems that trim("ab" from "abcba")should return "c", according to > > https://calcite.apache.org/docs/reference.html > > However calcite removes only "a" of "ab", and this trim() returs "bcb". See > > implementation of SqlFunctions.trim() > > Is it a bug or something? :) > > > > Zhong Yu
