db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-1623) Add ANSI TRIM implementation
Date Tue, 27 Mar 2007 19:53:32 GMT

    [ https://issues.apache.org/jira/browse/DERBY-1623?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12484566
] 

Bernt M. Johnsen commented on DERBY-1623:
-----------------------------------------

The standard says (Ch. 6.29):

Syntax Rules:

  11) If <trim function> is specified, then
    a) Case:
      i) If FROM is specified, then:
        1) Either <trim specification> or <trim character> or both shall be specified.
        2) If <trim specification> is not specified, then BOTH is implicit.
        3) If <trim character> is not specified, then ' ' is implicit.
      ii) Otherwise, let SRC be <trim source>. TRIM ( SRC ) is equivalent to TRIM (
BOTH ' ' FROM SRC ).
    b) Case:
      i) If the declared type of <character value expression> is fixed-length character
string or variablelength
         character string, then the declared type of the <trim function> is variable-length
character
         string with maximum length equal to the fixed length or maximum variable length of
the <trim
         source>.
      ii) Otherwise, the declared type of the <trim function> is a character large object
type with maximum
          length equal to the maximum variable length of the <trim source>.
    c) If a <trim character> is specified, then <trim character> and <trim
source> shall be comparable.
    d) The declared type of the <trim function> is that of the <trim source>.
 
General Rules:
 9) 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 either S or SC is the null value, then the result of the <trim function> is
the null value.
    d) If the length in characters of SC is not 1 (one), then an exception condition is raised:
data exception — trim error.
    e) Case:
      i) If BOTH is specified or if no <trim specification> is specified, then the result
of the <trim function> is the value of S with any leading or trailing characters equal
to SC removed.
      ii) If TRAILING is specified, then the result of the <trim function> is the value
of S with any trailing characters equal to SC removed.
      iii) If LEADING is specified, then the result of the <trim function> is the value
of S with any leading characters equal to SC removed.

So we can safely assume that <trim character> is one character.

Another clarification: Both <trim character> and <trim source> are <character
value expression> which means they may be literals, expressions (e.g string functions,
concatenations etc) or column refernces. Thus

create table tt (v varchar(256), t varchar(10));
select TRIM(TRAILING t FROM v);

is legal as long as that values of t is of length 1.




> Add ANSI TRIM implementation
> ----------------------------
>
>                 Key: DERBY-1623
>                 URL: https://issues.apache.org/jira/browse/DERBY-1623
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: Emmanuel Bernard
>         Assigned To: Manish Khettry
>
> JPA is requiring databases to support this ANSI feature esp the ability to chose the
trimmed character
> TRIM([ [ LEADING | TRAILING | BOTH ] [ chars ] FROM ] str)

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message