/*
141.TAN
TAN(<number>)
返回正切值
TANH(<number>)
返回指定数值参数的双曲正切值。
*/
SELECT TAN (0.0) FROM DUMMY; SELECT TANH(1) FROM DUMMY;
/*
142. 类型转换
TO_ALPHANUM(<value>)
转换为alphanum类型
TO_BIGINT(<value>)
转换为bigint类型
TO_BINARY(<value>)
转换为binary类型
TO_BLOB(<value>)
<value> ::= <binary string> | <nclob_value> | <clob_value>
转换为BLOB数据类型
TO_BOOLEAN(<value>)
转换为boolean类型
1，‘true’,true返回1;
0,‘false‘,false返回0;
unknown,‘unknown‘返回值；
大小写不敏感；
TO_CLOB(<value>)
转换为CLOB类型
TO_DATE(<date> [, <format>])
转换为DATE类型
TO_DATS(<date>)
Converts a date string into an ABAP DATE string.
TO_DECIMAL(<value> [, <precision>, <scale>])
转换WieDECIMAL类型
<precision>数位，1~38，默认34
<scale>小数位数
TO_DOUBLE(<value>)
转换为DOUBLE类型
TO_FIXEDCHAR(<string>, <size>)
转换<size>长度字符
TO_INT(<value>)
转换为INT类型
TO_INTEGER(<value>)
转换为INTEGER类型
TO_JSON_BOOLEAN(<value>)
Converts a given <value> to a boolean value in JSON format.
TO_NCLOB(<value>)
转换为NCLOB类型
TO_NVARCHAR(<value> [, <format>])
转换为NVARCHAR类型
TO_VARCHAR(<value> [, <format>])
转换为VARCHAR类型
--转换符号
--9 - Return the number in the specified position; otherwise, return nothing.
--0 - Return the number in the specified position; otherwise, return a zero (0).
--S - Return the sign symbol (either + or -) for the value.
--E - Divide the number into significant part and exponent part.
--% - Multiply <value> by 10^2 and adds a percent symbol (%) at the end.
--. (a period) - Insert a period in the specified position.
--All other characters other than the items above: Return the character in the specified position.
TO_REAL(<value>)
转换为REAL类型
TO_SECONDDATE(<date> [, <format>])
转换为SECONDDATE数据类型
TO_SMALLDECIMAL(<value>)
转换为SMALLDECIMAL类型
TO_SMALLINT(<value>)
转换为SMALLINT类型
TO_TIME(<time> [, <format>])
转换为TIME类型
TO_TIMESTAMP(<date> [, <format>])
转换为TIMESTAMP类型
TO_TINYINT(<value>)
转换为TINYINT类型
*/
SELECT TO_ALPHANUM (‘10‘) FROM DUMMY; SELECT TO_BIGINT (‘10‘) FROM DUMMY; SELECT TO_BIGINT (10.1) FROM DUMMY; SELECT TO_BINARY (‘abc‘) FROM DUMMY; SELECT TO_BLOB(TO_BINARY(‘abcde‘)) "to blob" FROM DUMMY; SELECT TO_BLOB(TO_CLOB(‘abc‘)) "to blob" FROM DUMMY; SELECT TO_BOOLEAN(0) FROM DUMMY; SELECT TO_BOOLEAN(‘true‘) FROM DUMMY; SELECT TO_BOOLEAN(‘False‘) FROM DUMMY; SELECT TO_CLOB (‘TO_CLOB converts the value to a CLOB data type‘) FROM DUMMY; SELECT TO_DATE(‘2010/01/12‘, ‘YYYY/MM/DD‘) FROM DUMMY; --YYYYMMDD类型 SELECT TO_DATS (‘2010-01-12‘) "abap date" FROM DUMMY; SELECT TO_DECIMAL(7654321.888888, 10, 3) FROM DUMMY; SELECT TO_DOUBLE (‘15.12‘) FROM DUMMY; --返回An SELECT TO_FIXEDCHAR (‘Ant‘, 2) FROM DUMMY; SELECT TO_INT (123.3) FROM DUMMY; SELECT TO_INTEGER (‘11‘) FROM DUMMY; --创建COLLECTION, --CREATE COLLECTION TEST_COLLECTION; --INSERT INTO TEST_COLLECTION VALUES (‘{"k1" : true}‘); --SELECT * FROM TEST_COLLECTION WHERE "k1" = TO_JSON_BOOLEAN(TRUE); SELECT TO_NCLOB (‘TO_NCLOB converts the value to a NCLOB data type‘) FROM DUMMY; --转换日期格式 SELECT TO_NVARCHAR(TO_DATE(‘2009/12/31‘), ‘YY-MM-DD‘) FROM DUMMY; SELECT TO_NVARCHAR(1, ‘00.00‘) FROM Dummy; --> 01.00 SELECT TO_NVARCHAR(100, ‘00.00‘) FROM Dummy; --> 100.00 SELECT TO_NVARCHAR(100, ‘9999.00‘) FROM Dummy; --> 100.00 SELECT TO_NVARCHAR(100, ‘0000.00‘) FROM Dummy; --> 0100.00 SELECT TO_NVARCHAR(100, ‘S0000.00‘) FROM Dummy; --> +0100.00 SELECT TO_NVARCHAR(-100, ‘S0000.00‘) FROM Dummy; --> -0100.00 SELECT TO_NVARCHAR(-100, ‘S0.0E0‘) FROM Dummy; --> -1.0E2 SELECT TO_NVARCHAR(-0.001, ‘S0.0E0‘) FROM Dummy; --> -1.0E-3 SELECT TO_NVARCHAR(-0.001, ‘S0.0E00‘) FROM Dummy; --> -1.0E-03 SELECT TO_NVARCHAR(1000, ‘9,999.00‘) FROM Dummy; --> 1,000.00 SELECT TO_NVARCHAR(1000, ‘$9,999.00‘) FROM Dummy; --> $1,000.00 SELECT TO_NVARCHAR(1000, ‘$9,999.99‘) FROM Dummy; --> $1,000. SELECT 3*TO_REAL (‘15.12‘) FROM DUMMY; SELECT TO_SECONDDATE (‘2010-01-11 13:30:00‘, ‘YYYY-MM-DD HH24:MI:SS‘) FROM DUMMY; SELECT TO_SMALLDECIMAL(7654321.89) FROM DUMMY; SELECT TO_SMALLINT(10.5) FROM DUMMY; SELECT TO_TIME(‘08:30 AM‘, ‘HH:MI AM‘) FROM DUMMY; SELECT TO_TIMESTAMP (‘2010-01-11 13:30:00‘, ‘YYYY-MM-DD HH24:MI:SS‘) FROM DUMMY; SELECT TO_TINYINT (‘10‘) FROM DUMMY;
/*
143.TRIM
TRIM([[LEADING | TRAILING | BOTH] <trim_char> FROM] <string> )
去除前置or后置字符
默认BOTH
TRIM_ARRAY(<array_value_expression>, <truncate_length>)
去除Array中指定数目元素
*/
--去除前置，后置字符 SELECT TRIM (‘a‘ FROM ‘aaa123456789aa‘) FROM DUMMY; --去除前置字符 SELECT TRIM (LEADING ‘a‘ FROM ‘aaa123456789aa‘) FROM DUMMY; --移除3个元素 SELECT TRIM_ARRAY(VAL, 3) FROM ARRAY_TEST;