Conditional concatenation issue

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Conditional concatenation issue

Andreas Säger
Hello,

CREATE TABLE TESTTABLE(EMAIL VARCHAR(32) PRIMARY KEY);
INSERT INTO TESTATBLE VALUES('mailto:[hidden email]');
INSERT INTO TESTATBLE VALUES('[hidden email]');

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:[hidden email]
mailto:[hidden email]

whereas HSQL 2.3 returns
       mailto:[hidden email]
mailto:[hidden email]
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Conditional concatenation issue

Fred Toussi-2
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:[hidden email]');
> INSERT INTO TESTATBLE VALUES('[hidden email]');
>
> 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:[hidden email]
> mailto:[hidden email]
>
> whereas HSQL 2.3 returns
>        mailto:[hidden email]
> mailto:[hidden email]
> 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
> [hidden email]
> 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
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user