Nikita Pande created PHOENIX-7200:
-------------------------------------
Summary: Support LEFT, RIGHT, STRIP, DIGITS, CHR, DAYS operators
as built in functions
Key: PHOENIX-7200
URL: https://issues.apache.org/jira/browse/PHOENIX-7200
Project: Phoenix
Issue Type: Improvement
Reporter: Nikita Pande
When we are validating phoenix with existing databases in our organisation,
there are few gaps identified wrt built in functions.
1. LEFT: [https://www.ibm.com/docs/en/db2-for-zos/12?topic=functions-left
|http://example.com]
*Description*: The LEFT function returns a string that consists of the
specified number of
leftmost bytes of the specified string units.
*Example*: Assume that host variable ALPHA has a value of 'ABCDEF'. The
following
statement returns '*ABC*'
{code:java}
SELECT LEFT(:ALPHA,3) FROM SYSIBM.SYSDUMMY1;
{code}
2. RIGHT:[
https://www.ibm.com/docs/en/db2-for-zos/12?topic=functions-right|http://example.com]
*Description*: The RIGHT function returns a string that consists of the
specified number
of rightmost bytes or specified string unit from a string.
*Example*: Assume that host variable ALPHA has a value of 'ABCDEF'. The
following
statement returns the value '*DEF*', which are the three rightmost
characters in ALPHA
{code:java}
SELECT RIGHT(ALPHA,3) FROM SYSIBM.SYSDUMMY1;
{code}
3. STRIP: [https://www.ibm.com/docs/en/db2-for-zos/12?topic=functions-strip]
*Description*: The STRIP function removes blanks or another specified
character from
the end, the beginning, or both ends of a string expression.
*Example*: Remove a specific character from a string, o/p is *Hello World *
{code:java}
SELECT STRIP('---Hello World---', B, '-') AS StrippedString FROM
SYSIBM.SYSDUMMY1;
{code}
4. DIGITS: [https://www.ibm.com/docs/en/db2-for-zos/12?topic=functions-digits]
*Description*: The DIGITS function returns a character string
representation of the
absolute value of a number.
Example: Assume that COLUMNX has the data type DECIMAL(6,2), and that one
of its
values is *-6.28*. For this value, the following statement returns the
value *'000628'.*
{code:java}
DIGITS(COLUMNX)
{code}
5. CHR: [https://www.ibm.com/docs/en/db2-for-zos/12?topic=functions-chr]
*Description*: The CHR function returns the character that has the ASCII
code value that
is specified by the argument.
Example: Set :hv with the Euro symbol "€" in CCSID 923:
{code:java}
SET :hv = CHR(164); -- x'A4'
{code}
6. DAYS: [https://www.ibm.com/docs/en/db2-for-zos/12?topic=functions-days]
*Description*: The DAYS function converts each date to a number (the
number of days
since '0001-01-01'), and subtracting these numbers gives the number of
days between
the two dates. o/p is *364* since 2022 is not a leap year
*Example*:
{code:java}
SELECT (DAYS('2022-12-31') - DAYS('2022-01-01')) AS days_difference
FROM sysibm.sysdummy1;
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
