Uploaded image for project: 'Engineering'
  1. Engineering
  2. ENG-3233

As a user, I want to use the simplest SQL STRING-returning functions, so that I can migrate my SQL app that uses them.



    • Team Backlog:
      CORE team
    • Story Points:
    • Sprint:
      core29, core30, core31


      Done so far:
      Function "REPEAT", "CONCAT" and its "||", LOWER, UPPER.


      For ease of implementation (no parser changes) this set includes only SQL 2003 and JDBC standard functions already supported by the hsqldb parser.

      These functions need unit tests and SQLCoverage test cases.

      [NEWS -- adding parser support if missing has become relatively easy with recent developments (and practice)]
      [NEWS -- some items below were implemented by Xin since this ticket was sized -- and have been prefixed with DONE! in the list, below.]

      CHAR ( <INTEGER UNICODE code> ) – (from JDBC)

      OVERLAY ( <string expr 1> PLACING <string expr 2> FROM <start position> [ FOR <string length> ] [ USING CHARACTERS ] ) – The character version of OVERLAY returns a character string based on <string expr 1> in which <string length> characters have been removed from the <start position> and in their place, the whole <string expr 2> is copied. (SQL 2003)

      REPLACE ( <string expr 1>, <string expr 2> [, <string expr 3> ] ) – Returns a character string based on <string expr 1> where each occurrence of <string expr 2> has been replaced with a copy of <string expr 3>. If the function is called with just two arguments, the <string expr 3> defaults to the empty string and calling the function simply removes the occurrences of <string expr 2> from the first string. (from JDBC)

      TRIM ([ [ LEADING | TRAILING | BOTH ] [ <trim character> ] FROM ] <string expr> ) – Consecutive instances of <trim character> are removed from the beginning, the end or both ends of the <string expr> depending on the value of the optional first qualifier [ LEADING | TRAILING | BOTH ]. If no qualifier is specified, BOTH is used as default. If [ <trim character> ] is not specified, the space character is used as default.

      Stretch goal: also add these functions which MAY stretch the capabilities of the hsql parser:
      OVERLAY ( <string expr 1> PLACING <string expr 2> FROM <start position> [ FOR <string length> ] USING OCTETS ) – (SQL 2003)
      SUBSTRING ( <string expr> FROM <start position> [ FOR <string length> ] USING OCTETS ) –

      • These may be deferred if HSQL does not easily support them. These function will be allowed to corrupt UTF8 strings by interrupting multi-byte sequences. Operating on the result as a (corrupted) UTF-8 string may throw exceptions, but will fail gracefully.

      – other hsql-supported functions that could easily be added on later as aliases to these, possibly with hard-coded parameters (See ENG-3283).
      – other SQL 2003, JDBC, or MySQL functions that do not have hsql parser support.


          Issue Links



              xin Xin Jia
              pmartel Paul Martel
              1 Vote for this issue
              3 Start watching this issue



                  Zendesk Support