【SAP Hana】X档案：HANA SQL 常用函数使用示例
HANA SQL 常用函数使用示例
- 1、日期时间类
- （1）获取系统日期时间
- （2）日期时间&字符串相互转换
- （3）日期时间截取
- （4）获取日期相关信息
- （5）日期时间差
- （6）日期时间加减
- （7）获取相关日期
- 2、字符串处理类
- （1）数据类型转换
- （2）大小写转换
- （3）字符串拼接
- （4）字符串截取
- （5）字符串填充和替换
- 3、数学函数
- （1）正负相关
- （2）小数相关
- （3）取最大最小值
- （4）数学计算
- 4、其他函数
- （1）关于 NULL
- （2）系统信息
1、日期时间类
（1）获取系统日期时间
select
current_date "当前系统日期",
current_time "当前系统时间",
current_timestamp "当前系统的时间戳",
NOW() "当前系统的时间戳",
CURRENT_UTCDATE "UTC日期", --UTC 代表协调世界时，也被称为格林尼治标准时间（ GMT）
CURRENT_UTCTIME "UTC时间",
CURRENT_UTCTIMESTAMP "UTC时戳"
from dummy;
（2）日期时间&字符串相互转换
--1、字符串转换为日期时间（注：hana studio显示日期格式与设置有关）
SELECT
cast( 2021 || '-' || '09-' || 29 as date) "日期", -- 2021-09-21
TO_DATE('2021-09-29', 'YYYY-MM-DD') "日期", -- 2021-09-21
TO_DATE('2021-272', 'YYYY-DDD') "日期", -- 2021-09-21
TO_DATE('2021@09###29', 'YYYY@MM###DD') "日期", -- 2021-09-21
TO_DATE('2021-09-29 16:49:00') "日期", -- 2021-09-21
TO_DATE('2021/09/29') "日期", -- 2021-09-21
TO_DATE('20210929') "日期", -- 2021-09-21
TO_DATS('2021-09-29') "ABAP日期", -- 20210921
TO_DATS('2021/09/29') "ABAP日期", -- 20210921
TO_DATS('20210929') "ABAP日期", -- 20210921
TO_SECONDDATE('2021-09-29 16:49:00', 'YYYY-MM-DD HH24:MI:SS') "日期时间", -- 2021-09-21 16:49:00.0
TO_TIME('04:49 PM', 'HH:MI AM') "时间", -- 4:49:00 PM
TO_TIMESTAMP('2021-09-29 16:49:00.001', 'YYYY-MM-DD HH24:MI:SS.FF3') "时戳" -- 2021-09-21 16:49:00.001
FROM DUMMY;
说明：
语法：CAST (expression AS data_type)，
其中，expression 是表达式；
data_type，日期时间相关的目标数据类型，可以有：DAYDATE | DATE、SECONDDATE、TIME 、TIMESTAMP。
语法：TO_DATS( d|dt )，不带format；
TO_DATE / TO_SECONDDATE / TO_TIMESTAMP ( d|dt [, format] )；
TO_TIME( dt|t [, format] )；
其中，format用于描述待转换的字符串的格式，而非转换后的格式。
format格式，主要有：
（1）日期默认格式：‘YYYY-MM-DD’，
其他日期格式：‘YYYY/MM/DD’、‘YYYY/MM-DD’、‘YYYY-MM/DD’、‘YYYYMMDD’（其中MM还可以有：MON三位月份英文缩写，MONTH月份英文全名，RM月份罗马数字）、‘YYYY-DDD’（其中DDD为一年中的第几天1-366）。
（2）TIME 类型的默认格式为‘HH24:MI:SS’。
（3）SECONDDATE 类型的默认格式为‘YYYY-MM-DD HH24:MI:SS’。
（4）TIMESTAMP 数据类型由日期和时间信息组成时戳。默认格式为‘YYYY-MM-DD HH24:MI:SS.FF7’。 FFn 代表含有小数的秒，其中 n 表示小数部分的数字位数，1-7位。
--2、日期时间转换为字符串（注：输出为字符串，与hana studio显示日期格式设置无关）
do begin
declare mydate date default '2021-09-29'; --or '2021/09/29' or '20210929'
declare mydateordatestr varchar(10) default '2021-09-29'; --or '2021/09/29' or '20210929'
SELECT
cast( :mydate as varchar(10)) "日期字符串", --2009-12-31(default)
TO_CHAR( :mydateordatestr, 'YYYY/MM/DD' ) "日期字符串", --2009/12/31
TO_CHAR( :mydateordatestr ) "日期字符串", --2009-12-31(default)
TO_NCHAR( :mydateordatestr, 'YYYY/MM/DD' ) "日期字符串", --2009/12/31
TO_VARCHAR( :mydateordatestr, 'YYYY/MM/DD' ) "日期字符串", --2009/12/31
TO_NVARCHAR( :mydateordatestr, 'YY-MM-DD' ) "日期字符串" --09-12-31
FROM DUMMY;
end;
说明：
语法：CAST (expression AS data_type)，
其中，expression 是表达式；
data_type，字符相关的目标数据类型，可以有：CHAR、NCHAR、VARCHAR 、NVARCHAR。
语法：TO_CHAR / TO_NCHAR / TO_VARCHAR / TO_NVARCHAR ( value [, format] )，
将value转换为format指定的格式的字符串。
--3、UTC转换
do begin
declare mydtordtstr varchar(30) default '2021-09-29 16:49:00'; --or '20210929164901'
SELECT
LOCALTOUTC( :mydtordtstr, 'EST') "本地时间转UTC时间", -- 2021-09-29 20:49:00.0
UTCTOLOCAL( :mydtordtstr, 'EST') "UTC时间转本地时间" -- 2021-09-29 12:49:00.0
FROM DUMMY;
end;
（3）日期时间截取
-- 方法一：用不同的函数来获取
do begin
declare mydtordtstr varchar(30) default '2021-09-29 16:49:00';
SELECT
YEAR ( :mydtordtstr ) "年度数", -- 2021
MONTH( :mydtordtstr ) "月份数，从1开始", -- 9
DAYOFMONTH( :mydtordtstr ) "当月第几天", -- 29
HOUR( :mydtordtstr ) "小时数", -- 16
MINUTE( :mydtordtstr ) "分钟数", -- 49
SECOND( :mydtordtstr ) "秒数" -- 0
FROM DUMMY;
end;
-- 方法二：用 EXTRACT ({YEAR | MONTH | DAY | HOUR | MINUTE | SECOND} FROM d) 获取指定的日期时间字段的值（年、月、日、时、分、秒）。
do begin
declare mydtordtstr varchar(30) default '2021-09-29 16:49:00';
SELECT
EXTRACT( YEAR FROM :mydtordtstr ) "年度数", -- 2021
EXTRACT( MONTH FROM :mydtordtstr ) "月份数，从1开始" , -- 9
EXTRACT( DAY FROM :mydtordtstr ) "当月第几天" , -- 29
EXTRACT( HOUR FROM :mydtordtstr ) "小时数", -- 16
EXTRACT( MINUTE FROM :mydtordtstr ) "分钟数", -- 49
EXTRACT( SECOND FROM :mydtordtstr ) "秒数" -- 0
FROM DUMMY;
end;
（4）获取日期相关信息
do begin
declare mydateordatestr varchar(10) default '2021-09-29'; --or '2021/09/29' or '20210929'
SELECT
DAYOFMONTH( :mydateordatestr ) "当月第几天", --29
DAYOFYEAR( :mydateordatestr ) "当年第几天", --272
WEEK( :mydateordatestr ) "当年第几周", --40
WEEKDAY( :mydateordatestr ) "周几，从0开始，周一为0", --2
DAYNAME( :mydateordatestr ) "周几英文名", --WEDNESDAY
MONTHNAME( :mydateordatestr ) "月份英文名", --SEPTEMBER
ISOWEEK( :mydateordatestr ) "年份第几周", --2021-W39
WEEK( '20210101' ) "当年第几周", --1
ISOWEEK( '20210101' ) "年度第几周", --2020-W53
QUARTER( :mydateordatestr, 1 ) "年份季度", --2021-Q3
RIGHT(QUARTER( :mydateordatestr, 1 ), 1 ) "季度" --3
FROM DUMMY;
end;
说明：
语法：WEEKDAY(d) 返回代表日期 d 所在星期的日期数字（星期几）。
注意，返回值范围为 0 至 6，表示 Monday(0)至 Sunday(6)。
语法：QUARTER(d, [, start_month ]) 返回日期 d 的年份季度。
其中，第一季度由 start_month 定义的月份开始，如果没有定义start_month，第一季度假设为从一月开始。
（5）日期时间差
do begin
declare mydateordatestr varchar(10) default '2021-09-29'; --or '2021/09/29' or '20210929'
declare mydtordtstr varchar(30) default '2021-09-29 16:49:00'; --or '20210929164901'
SELECT
years_between( :mydateordatestr, now() ) "年度数",
months_between( :mydateordatestr, now() ) "月份数",
DAYS_BETWEEN( :mydateordatestr, now() ) "天数",
SECONDS_BETWEEN( :mydtordtstr, now() ) "秒数"
FROM DUMMY;
end;
（6）日期时间加减
do begin
declare mydateordatestr varchar(10) default '2021-09-29'; --or '2021/09/29' or '20210929'
declare mydtordtstr varchar(30) default '2021-09-29 16:49:00'; --or '20210929164901'
SELECT
ADD_DAYS( :mydateordatestr, 100) "100天", -- 2022-01-07 12:00:00 AM
ADD_MONTHS( :mydateordatestr, -10) "10月前", -- 2020-12-29 12:00:00 AM
ADD_YEARS( :mydateordatestr, 1) "1周年", -- 2022-09-29 12:00:00 AM
ADD_SECONDS( :mydtordtstr, 1000) "1千秒" -- 5:05:40 PM
FROM DUMMY;
end;
（7）获取相关日期
do begin
declare mydateordatestr varchar(10) default '2021-09-29'; --or '2021/09/29' or '20210929'
SELECT
NEXT_DAY( :mydateordatestr ) "下一天", -- 2021-09-30
LAST_DAY( :mydateordatestr ) "本月底" -- 2021-09-30
FROM DUMMY;
end;
2、字符串处理类
（1）数据类型转换
--1、cast转换：将表达式转换为目标数据类型。
SELECT
cast( 1 + 5.5 as varchar ) "表达式求和后转字符串", --6.5
to_varchar(1 + 5.5) "表达式求和后转字符串", --6.5
cast( 1 || 'A' as varchar ) "表达式合并后转字符串", --1A
to_varchar(1 || 'A') "表达式合并后转字符串" --1A
FROM DUMMY;
--2、字符串转换
do begin
declare mydateordatestr varchar(10) default '2021-09-29'; --or '2021/09/29' or '20210929'
SELECT
TO_CHAR ( 123.45 ) "数值转字符串", --123.45
TO_CHAR ( 4*5 ) "表达式结果转字符串", --20
TO_CHAR( :mydateordatestr, 'YYYY/MM/DD' ) "转指定日期格式字符串", --2021/09/29
TO_CHAR( :mydateordatestr ) "转默认日期格式字符串", --2021-09-29
TO_NCHAR( :mydateordatestr, 'YYYY/MM/DD' ) "转指定日期格式字符串", --2021/09/29
TO_VARCHAR( :mydateordatestr, 'YYYY/MM/DD' ) "转指定日期格式字符串", --2021/09/29
TO_NVARCHAR( :mydateordatestr, 'YY-MM-DD' ) "转指定日期格式字符串" --21-09-31
FROM DUMMY;
end;
--3、字符串转数字
SELECT
TO_INT('123') "转32位整型",
TO_INTEGER('123') "转32位整型",
TO_TINYINT('123') "转8位无符号整型",
TO_SMALLINT('123') "转16位无符号整型",
TO_BIGINT('123') "转64位整型"
FROM DUMMY;
SELECT
TO_DECIMAL('7654321.888888', 10, 3) "转decimal类型", --7,654,321.888
TO_SMALLDECIMAL('7654321.888888') "转smalldecimal类型", --7,654,321.888888
TO_DOUBLE ('15.12') "转双精度数据类型", --15.12
TO_REAL ('15.12') "转单精度数据类型" --15.119999885559082
FROM DUMMY;
--4、ASCII互转：
SELECT
ASCII('XLevon') "ASCII", --88，仅对字符串首字母处理
CHAR(88) "CHAR" --X，返回88表示的ASCII字符
FROM DUMMY;
--5、UNICODE互转：
SELECT
UNICODE('XLevon') "UNICODE", --88，仅对字符串首字母处理，同ASCII码
NCHAR(88) "NCHAR", --X，返回UNICODE字符
UNICODE('我是中国人') "UNICODE", --25105
NCHAR(25105) "NCHAR" --我
FROM DUMMY;
--6、进制转换
SELECT
TO_BINARY('XLevon') "转二进制", --584C65766F6E
BINTOHEX('XLevon') "二进制转十六进制", --584C65766F6E
HEXTOBIN('123AB') "十六进制转二进制" --0123AB
FROM DUMMY;
--7、大对象转换
SELECT
TO_BLOB(TO_BINARY('abcde')) "TO_BLOB", --参数值必须是二进制字符串
TO_CLOB('TO_CLOB converts the value to a CLOB data type') "TO_CLOB",
TO_NCLOB('TO_NCLOB converts the value to a NCLOB data type') "TO_NCLOB"
FROM DUMMY;
（2）大小写转换
SELECT
LCASE('XLevon') "LCASE", LOWER('XLevon') "LOWER", --转小写：xlevon xlevon
UCASE('XLevon') "UCASE", UPPER('XLevon') "UPPER" --转大写：XLEVON XLEVON
FROM DUMMY;
（3）字符串拼接
SELECT
CONCAT('X', 'Levon') "CONCAT", --XLevon，只能做两段拼接
CHAR(88) || 'L' || 'evon' "||" --XLevon，能做多段拼接
FROM DUMMY;
（4）字符串截取
--1、获取字符串长度：返回字符串 str 中的字符数。对于大对象(LOB)类型，该函数返回对象的字节长度。
SELECT LENGTH ('XLevon') "LENGTH" --6
FROM DUMMY;
--2、左右截取指定位数
SELECT
LEFT('XLevon', 1) "LEFT1", --X
RIGHT('XLevon', 5) "RIGHT5" --Levon
FROM DUMMY;
--3、左右截取指定字符或字符串
SELECT
TRIM( ' XLevon ' ) "去两端空格", --XLevon
TRIM( 'ab' FROM 'aba12345baa' ) "去两端的a和b，而不是ab", --12345
TRIM( LEADING 'a' FROM 'aba12345baa' ) "去左端的a", --ba12345baa
LTRIM( 'aba12345baa','ab' ) "去左端的a和b", --12345baa
RTRIM( 'aba12345baa','ab' ) "去右端的a和b" --aba12345
FROM DUMMY;
说明：
语法：TRIM ([[LEADING | TRAILING | BOTH] trim_char_set FROM] str )，
返回移除前导和后置 trim_char_set 中的字符 后的字符串 str。
截断操作从起始（LEADING）、结尾（TRAILING）或者两端（BOTH，默认）执行。
如果 str 或者 trim_char_set 为空，则返回 NULL。
如果没有指定可选项， TRIM 移除字符串 str 中两端的 trim_char_set 中的字符 。
如果没有指定 trim_char_set，则默认使用单个空格，即去空格。
语法：LTRIM (str [, remove_set])，
返回字符串 str 截取所有前导空格后的值。
如果定义了 remove_set， LTRIM 从起始位置移除字符串str 包含该集合中的字符，该过程持续至到达不在 remove_set 中的字符，等同 TRIM ( LEADING remove_set FROM str )。
如果没有指定 remove_set，则默认使用单个空格，即去左空格，等同 TRIM ( LEADING FROM str )。
语法：RTRIM (str [,remove_set ])
返回字符串 str 截取所有后置空格后的值。如果定义了 remove_set， RTRIM 从尾部位置移除字符串 str 包含该集合中的字符，该过程持续至到达不在 remove_set 中的字符，等同 TRIM ( TRAILING remove_set FROM str )。
如果没有指定 remove_set，则默认使用单个空格，即去右空格，等同 TRIM ( TRAILING FROM str )。
--4、定位字符串
SELECT
LOCATE ('Learn hana sql with XLevon', 'Learn') "LOCATE1", --1
LOCATE ('Learn hana sql with XLevon', 'XLevon') "LOCATE2", --21
LOCATE ('Learn hana sql with XLevon', 'Hana') "LOCATE3" --0，未找到
FROM DUMMY;
--5、任意截取
SELECT
SUBSTRING('Learn hana sql with XLevon',7,4) "SUBSTRING" , --hana
SUBSTR_BEFORE('Learn hana sql with XLevon','with') "SUBSTR_BEFORE", --Learn hana sql
SUBSTR_AFTER('Learn hana sql with XLevon','with') "SUBSTR_AFTER" --XLevon
FROM DUMMY;
说明：
语法：SUBSTRING (str, start_position [, string_length])，
返回字符串 str 从 start_position 开始，长度为 string_length 的子字符串。
string_length 未赋值时，返回从 start_position 起的剩余部分字符。
如果 start_position 小于等于 0，则被视为 1。
如果 string_length 小于 1，则返回空字符串。
语法：SUBSTR_BEFORE (str, pattern)，
返回 str 中位于 pattern 第一次出现位置前的子字符串。
如果 str 不包含 pattern 子字符串，则返回空字符串。
如果 pattern 为空字符串，则返回 str。
如果 str 或者 pattern 为 NULL，则返回 NULL。
语法：SUBSTR_AFTER (str, pattern)，
返回 str 中位于 pattern 第一次出现位置后的子字符串。
如果 str 不包含 pattern 子字符串，则返回空字符串。
如果 pattern 为空字符串，则返回 str。
如果 str 或者 pattern 为 NULL，则返回 NULL。
（5）字符串填充和替换
SELECT
LPAD ('end', 15, '12345') "lpad",
RPAD ('end', 15, '12345') "right padded"
FROM DUMMY;--123451234512end
说明：
语法：LPAD (str, n [, pattern]) ，
从左边开始对字符串 str 使用空格进行填充，达到 n 指定的长度。
如果指定了 pattern 参数，字符串 str 将按顺序填充直到满足 n 指定的长度。
语法：RPAD (str, n [, pattern])，
从尾部开始对字符串 str 使用空格进行填充，达到 n 指定的长度。
如果指定了 pattern 参数，字符串 str 将按顺序填充直到满足 n 指定的长度。
SELECT
REPLACE('abcbbcbcbd','bcb', '') "REPLACE" --acbd
FROM DUMMY;
说明：
语法：REPLACE ( original_string, search_string, replace_string)，
搜索 original_string 所有出现的 search_string，并用 replace_string 替换。
如果 original_string 未出现 search_string，则返回未修改的 original_string。
如果 original_string 为空， 则返回值也为空。
如果 original_string 中两个重叠的子字符串与 search_string 匹配，只有第一个会被替换：
如果 original_string， search_string 或者 replace_string 为 NULL，则返回值也为 NULL。
3、数学函数
（1）正负相关
SELECT
ABS(-100) "绝对值", -- 100
SIGN(-100) "符号:1-正,-1-负,0-0", -- -1
UMINUS(-100) "取反", -- 100
-(-100) "取反", -- 100
UMINUS(100) "取反", -- 100
-(100) "取反" -- 100
FROM DUMMY;
（2）小数相关
SELECT
ROUND(16.16, 1) "保留一位小数", --16.2
ROUND(16.16, -1) "往十位取整", --20
CEIL(14.5) "大于等于的最小整数", --15
FLOOR(14.5) "小于等于的最大整数" --14
FROM DUMMY;
说明：
语法：ROUND (n [, pos])，返回参数 n 小数点后 pos 位置的值（四舍五入）。
pos正数，表示保留的小数位数；
pos为0，表示保留整数；
pos为负数，表示往高位取整。
语法：CEIL(n) ，返回大于或者等于 n 的第一个整数。
语法：FLOOR (n)，返回不大于参数 n 的最大整数。
（3）取最大最小值
SELECT
GREATEST (2021, 09, 29, 16, 49) "数值最大值", --2021
GREATEST ('Learn', 'hana', 'sql', 'with', 'XLevon') "字符最大值", --with
GREATEST('2021-09-29', now(), '20080808' ) "日期最大值", --now()
LEAST(2021, 09, 29, 16, 49) "数值最小值", --9
LEAST('2021-09-29', now(), '20080808' ) "日期最小值" --2008-08-08
FROM DUMMY;
（4）数学计算
--1、取模
SELECT
MOD( 15, 4 ) "正数取模", -- 3
sign( 15 ) * mod( abs(15), abs(4) ) "取模计算逻辑", -- 3
MOD( -15, 4 ) "负数取模", -- -3
sign( -15 ) * mod( abs(-15), abs(4) ) "取模计算逻辑" -- -3
FROM DUMMY;
--2、数学公式
SELECT
SQRT(2) "2的平方根", --1.4142135623730951
POWER(2, 10) "2的10次方", --1024
EXP(1) "e的1次方", --2.718281828459045，即e的值
LOG(10, 2) "以10为底2的自然对数值", --0.30102999566398114
LN(10) "10的自然对数" --2.302585092994046
FROM DUMMY;
--3、三角函数公式
SELECT
SIN(3.141592653589793/2) "正弦值", -- 1
COS (0.0) "余弦值", -- 1
TAN (0.0) "正切值", -- 0
COT (40) "余切值", -- -0.8950829176379128
ASIN (0.5) "反正弦值", -- 0.5235987755982989 范围为-1 至 1
ACOS (0.5) "反余弦值", -- 1.0471975511965979 范围为-1 至 1
ATAN (0.5) "反正切值", -- 0.4636476090008061 范围为无限
ATAN2 (1.0, 2.0) "两数比值的反正切值", -- 0.4636476090008061 和 ATAN(n/m)的结果一致
SINH (0.0) "双曲正弦值", -- 0
COSH (0.5) "双曲余弦值", -- 1.1276259652063807
TANH (1.0) "双曲正切值" -- 0.7615941559557649
FROM DUMMY;
--4、位运算
SELECT
BITAND (255, 123) "按位与", -- 123
BITOR (255, 123) "按位或", -- 255
BITNOT (255) "按位否", -- -256
BITXOR (255, 123) "按位异或", -- 132
BITCOUNT (255) "按位统计1" -- 8
FROM DUMMY;
4、其他函数
（1）关于 NULL
IFNULL(expression1, expression2) --返回输入中第一个不为 NULL 的表达式。
--如果 expression1 不为 NULL，则返回 expression1。
--如果 expression2 不为 NULL，则返回 expression2。
--如果输入表达式都为 NULL，则返回 NULL。" "
``
NULLIF(expression1, expression2) --NULLIF 比较两个输入表达式的值，如果第一个表达式等于第二个，NULLIF 返回 NULL。
--如果 expression1 不等于 expression2，NULLIF 返回 expression1。
--如果 expression2 为 NULL，NULLIF 返回 expression1。
--第一个参数不能是NULL。
COALESCE(expression1, expression2, ···) --返回 参数列表中第一个非 NULL 的表达式。
--如果所有的参数都为 NULL，则结果也为 NULL。 "
（2）系统信息
SELECT
CURRENT_CONNECTION "当前连接 ID",
CURRENT_SCHEMA "当前Schema名", --set schema newuser;
CURRENT_USER "当前用户名",
SESSION_CONTEXT('APPLICATION') "session APPLICATION", --HDBStudio
SESSION_CONTEXT('APPLICATIONUSER') "session APPLICATIONUSER", --操作系统用户
SESSION_CONTEXT('TRACEPROFILE') "session TRACEPROFILE",
SESSION_USER "当前会话的用户名",
SYSUUID "SAP HANA 连接实例"
FROM DUMMY;
说明：
SCHEMA可以使用 SET SCHEMA <schema_name>来切换。
语法：SESSION_CONTEXT(session_variable)，
返回分配给当前用户的 session_variable 值。
访问的 session_variable ，可以是预定义或者用户自定义。预定义的会话变量可以通过客户端设置的有’APPLICATION’，‘APPLICATIONUSER’以及’TRACEPROFILE’。
会话变量可以通过使用命令 SET [SESSION] <variable_name> = 来定义或者修改，使用 UNSET [SESSION] <variable_name>取消设置。
SESSION_CONTEXT 返回最大长度为 512 字符的 NVARCHAR 类型。