SAP HANA 常用函数

常用语句：

获取上年年份：SELECT TO_CHAR (current_date, 'YYYY')-1 "to char" FROM DUMMY

SAP HANA 常用函数：

 数据类型转换函数(Data Type Conversion Functions ):

CAST        语法：CAST (表达式AS 数据类型)  SELECT CAST (7 AS VARCHAR)"cast" FROM DUMMY;  结果----->7     把7转换成字符串。 把表达式转换成目标数据类型。

数据类型：TINYINT | SMALLINT | INTEGER | BIGINT | DECIMAL | SMALLDECIMAL | REAL | DOUBLE | ALPHANUM | VARCHAR | NVARCHAR | DAYDATE | DATE | TIME | SECONDDATE | TIMESTAMP

TO_ALPHANUM   语法： TO_ALPHANUM (value)    SELECT TO_ALPHANUM ('10')"to alphanum" FROM DUMMY; 结果----> 10    把value转换成字母数字类型

TO_BIGINT  语法：TO_BIGINT (value)     SELECT TO_BIGINT ('10')"to bigint" FROM DUMMY;  结果--------->10    把value转换成二进制类型

TO_BLOB   语法：

TO_CHAR  SELECT TO_CHAR (TO_DATE('2009-12-31'), 'YYYY/MM/DD') "to char" FROM DUMMY;

TO_CLOB

TO_DATE   SELECT TO_DATE('2010-01-12', 'YYYY-MM-DD') "to date" FROM DUMMY;

TO_DATS

TO_DECIMAL

TO_DOUBLE

TO_INT

TO_INTEGER

TO_NCHAR

TO_NCLOB

TO_NVARCHAR

TO_REAL

TO_SECONDDATE

TO_SMALLDECIMAL

TO_SMALLINT

TO_TIME

TO_TIMESTAMP

TO_TINYINT

TO_VARCHAR

 

日期/时间函数(DateTime Functions ):

 ADD_DAYS

ADD_MONTHS

ADD_SECONDS

ADD_YEARS

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

CURRENT_UTCDATE

CURRENT_UTCTIME

CURRENT_UTCTIMESTAMP

DAYNAME

DAYOFMONTH

DAYOFYEAR

DAYS_BETWEEN

EXTRACT

HOUR

ISOWEEK

LAST_DAY

LOCALTOUTC

MINUTE

MONTH

MONTHNAME

NEXT_DAY

NOW

QUARTER

SECOND

SECONDS_BETWEEN

UTCTOLOCAL

WEEK

WEEKDAY

YEAR

 

数字类型相关函数:(Number Functions)

ABS

ACOS

ASIN

ATAN

ATAN2

BINTOHEX

BITAND

CEIL

COS

COSH

COT

EXP

FLOOR

GREATEST

HEXTOBIN

LEAST

LN

LOG

MOD

POWER

ROUND

SIGN

SIN

SINH

SQRT

TAN

TANH

UMINUS

 

 

 

字符串相关函数：(String Functions)

ASCII            SELECT ASCII('Ant')"ascii" FROM DUMMY;   结果 ----> 65

CHAR           SELECT CHAR (65) || CHAR (110) || CHAR (116)"character" FROM DUMMY;

CONCAT    SELECT CONCAT ('C','at') "concat" FROM DUMMY;     结果---->Cat     CONCAT (str1, str2) 把字符串str1和str2拼接起来

LCASE          SELECT LCASE ('TesT')"lcase" FROM DUMMY;          结果------>test      把字符串全部转换成小写

LEFT            SELECT LEFT ('Hello', 3)"left" FROM DUMMY;            结果-------->Hel    

LENGTH     SELECT LENGTH ('length in char')"length" FROM DUMMY; 结果---->14 获取字符串的长度

LOCATE      SELECT LOCATE ('length in char','char') "locate" FROM DUMMY; 结果--->11 获取子字符串出现第一次的位置

LOWER       SELECT LOWER ('AnT')"lower" FROM DUMMY;   结果----->ant 把字符串转成小写

NCHAR

REPLACE

RIGHT

RPAD

RTRIM

SUBSTR_AFTER

SUBSTR_BEFORE

SUBSTRING

TRIM

UCASE

UNICODE

UPPER

其他函数:

 COALESCE

CURRENT_CONNECTION

CURRENT_SCHEMA

CURRENT_USER

GROUPING_ID

IFNULL

MAP

NULLIF

SESSION_CONTEXT

SESSION_USER

SYSUUID