Re: Conditional concatenation issue

Yes, this is a known difference between the versions and there are often
questions about it.

Version 2.x treats the type of string literals as CHARACTER(n), with n
being the length of the string. When two or more literal strings are in
a CASE WHEN expression, the combined type is CHARACTER(m), with m being
the maximum length. Therefore (THEN '' ELSE 'mailto:') will return
either a string of spaces or 'mailto:'.

Fred

On Thu, Feb 18, 2016, at 08:28, Andreas Säger wrote:
> Hello,
> 
> CREATE TABLE TESTTABLE(EMAIL VARCHAR(32) PRIMARY KEY);
> INSERT INTO TESTATBLE VALUES('mailto:[email protected]');
> INSERT INTO TESTATBLE VALUES('[email protected]');
> 
> SELECT
>   CASE WHEN SUBSTR("EMAIL",1,7)='mailto:'
>   THEN ''
>   ELSE 'mailto:'
>   END || EMAIL AS "URL"
> FROM "TESTTABLE"
> 
> HSQL 1.8 (Libre/OpenOffice) returns the expected result:
> mailto:[email protected]
> mailto:[email protected]
> 
> whereas HSQL 2.3 returns
>        mailto:[email protected]
> mailto:[email protected]
> with 7 left-padding spaces in the first record.
> 
> Workaround for HSQL 2.3:
> SELECT
>   CASE WHEN SUBSTR("EMAIL",1,7)='mailto:'
>   THEN EMAIL
>   ELSE 'mailto:'|| EMAIL
>   END AS "URL"
> FROM "TESTTABLE"
> 
> Thank you for your attention,
> Andreas Säger
> 
> 
> ------------------------------------------------------------------------------
> Site24x7 APM Insight: Get Deep Visibility into Application Performance
> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
> Monitor end-to-end web transactions and take corrective actions now
> Troubleshoot faster and improve end-user experience. Signup Now!
> http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
> _______________________________________________
> Hsqldb-user mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Site24x7 APM Insight: Get Deep Visibility into Application Performance
APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
Monitor end-to-end web transactions and take corrective actions now
Troubleshoot faster and improve end-user experience. Signup Now!
http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140