sqltranslate ()

阅读(2955) 标签: 翻译, 指定格式,

描述:

将标准SQL中的函数翻译成指定数据库中的格式。

语法:

sql.sqltranslate(dbtype)

备注:

将标准SQL中的函数翻译成指定数据库中的格式。默认支持的数据库类型有:ORACLE ,SQLSVR ,DB2 ,MYSQL ,HSQL ,TERADATA ,POSTGRES,除此之外用户还可以通过修改配置文件自定义数据库类型及函数。

标准函数对照:

字符串函数

标准函数

含义

oracle

sql server

db2

mysql

teradata

hsql

PostgresSQL

LOWER(str)

转成小写

 

 

 

 

 

 

 

UPPER(str)

转成大写

 

 

 

 

 

 

 

LTRIM(str)

删除前导空白

 

 

 

 

 

 

 

RTRIM(str)

删除后缀空白

 

 

 

 

 

 

 

TRIM(str)

删除两端空白

TRIM(str)

LTRIM(RTRIM(str))

LTRIM(RTRIM(str))

TRIM(str)

TRIM(str)

 

LTRIM(RTRIM(str))

SUBSTR(str,start,len)
SUBSTR(str,start)

子串

SUBSTR(str,start,len)
SUBSTR(str,start)

SUBSTRING(str,start,len)
SUBSTRING(str,start,len(str)-start+1)

SUBSTR(str,start,len)
SUBSTR(str,start)

SUBSTR(str,start,len)
SUBSTR(str,start)

SUBSTR(str,start,len)
SUBSTR(str,start)

SUBSTR(str,start,len)
SUBSTR(str,start)

SUBSTR(str,start,len)
SUBSTR(str,start)

LEN(str)

串长

LENGTH(str)

LEN(str)

LENGTH(str)

LENGTH(str)

CHAR_LENGTH(str)

CHAR_LENGTH(str)

CHAR_LENGTH(str)

INDEXOF(sub,str[,start])

子串的位置

INSTR(str,sub[,start])

CHARINDEX(sub,str[,start])

LOCATE(sub,str[,start])

LOCATE(sub,str[,start])

INDEX(str,sub)
3
个参数时:n/a

LOCATE(sub,str[,start])

INDEX(str,sub)
3
个参数时:n/a

LEFT(str,len)

str中最左边len个字符

SUBSTR(str,1,len)

LEFT(str,len)

LEFT(str,len)

LEFT(str,len)

SUBSTR(str,1,len)

LEFT(str,len)

SUBSTR(str,1,len)

RIGHT(str,len)

str中最右边len个字符

CASE WHEN len=0 THEN '' WHEN LENGTH(str)>len THEN SUBSTR(str,-len) ELSE str END

RIGHT(str,len)

RIGHT(str,len)

RIGHT(str,len)

CASE WHEN len=0 then '' WHEN CHAR_LENGTH(str)>len THEN SUBSTR(str,CHAR_LENGTH(str)-len,len) ELSE str END

RIGHT(str,len)

CASE WHEN len=0 then '' WHEN CHAR_LENGTH(str)>len THEN SUBSTR(str,CHAR_LENGTH(str)-len,len) ELSE str END

CONCAT(str1,str2)

连接2个字符串

CONCAT(str1,str2)

str1+str2

CONCAT(str1,str2)

CONCAT(str1,str2)

str1||str2

str1||str2

str1||str2

REPLACE(str,sub,rplc)

str中的sub替换成rplc

 

 

 

 

OREPLACE(str,sub,rplc)

 

 

数值函数

标准函数

含义

oracle

sql server

db2

mysql

teradata

hsql

PostgresSQL

ABS(x)

求绝对值

 

 

 

 

 

 

 

ACOS(x)

求反余弦

 

 

 

 

 

 

 

ASIN(x)

求反正弦

 

 

 

 

 

 

 

ATAN(x)

求反正切

 

 

 

 

 

 

 

ATAN2(x,y)

求反正切

ATAN2(x,y)

ATN2(x,y)

ATAN2(x,y)

ATAN2(x,y)

 

 

 

CEIL(x)

求大于等于x的最小整数

CEIL(x)

CEILING(x)

CEIL(x)/CEILING(x)

CEIL(x)/CEILING(x)

CEIL(x)

 

CEIL(x)

COS(x)

求余弦

 

 

 

 

 

 

 

EXP(x)

ex次幂

 

 

 

 

 

 

 

FLOOR(x)

求小于等于x的最大整数

 

 

 

 

 

 

 

LN(x)

求自然对数

LN(x)

LOG(x)

LN(x)/LOG(x)

LN(x)/LOG(x)

LN(x)

 

 

LOG10(x)

求以10为底的对数

LOG(10,x)

LOG10(x)

LOG10(x)

LOG10(x)

LOG(x)

LOG10(x)

LOG(x)

MOD(x,m)

xm

MOD(x,m)

x%m

MOD(x,m)

x%m

x MOD m

 

x%m

POWER(x,y)

xy次幂

 

 

 

 

 

 

 

ROUND(x,n)

四舍五入

 

 

 

 

 

 

 

SIGN(x)

求符号

 

 

 

 

 

 

 

SIN(x)

求正弦

 

 

 

 

 

 

 

SQRT(x)

平方根

 

 

 

 

 

 

 

TAN(x)

正切

 

 

 

 

 

 

 

TRUNC(x,n)

截断

TRUNC(x,n)

CONVERT(BIGINT,x*POWER(10,n))/POWER(10.0,n)

TRUNC(x,n)

TRUNCATE(x,n)

TRUNC(x,n)

 

 

RAND(seed)

随机数

DBMS_RANDOM.VALUE(0, seed)
//
返回0-seed之间的数

RAND(seed)//返回0-1之间的数值

RAND(seed)//返回0-1之间的数

RAND(seed)

RANDOM(0,seed)
//
返回0seed间的int

RAND(seed)
//
返回0-1之间的数

RANDOM()

时间函数

标准函数

含义

oracle

sql server

db2

mysql

teradata

hsql

PostgresSQL

YEAR(d)

取年

EXTRACT(YEAR FROM d)

YEAR(d)

YEAR(d)

YEAR(d)

EXTRACT(YEAR FROM d)/TD_YEAR_OF_CALENDAR(d)

YEAR(d)

EXTRACT(YEAR FROM d)

MONTH(d)

取月

EXTRACT(MONTH FROM d)

MONTH(d)

MONTH(d)

MONTH(d)

EXTRACT(MONTH FROM d)/TD_MONTH_OF_YEAR(d)

MONTH(d)

EXTRACT(MONTH FROM d)

DAY(d)

取天

EXTRACT(DAY FROM d)

DAY(d)

DAY(d)

DAY(d)

EXTRACT(DAY FROM d)/TD_DAY_OF_MONTH(d)

DAY(d)

EXTRACT(DAY FROM d)

HOUR(d)

取小时

EXTRACT(HOUR FROM d)

DATEPART(HH, d)

HOUR(d)

HOUR(d)

EXTRACT(HOUR FROM d)

HOUR(d)

EXTRACT(HOUR FROM d)

MINUTE(d)

取分钟

EXTRACT(MINUTE FROM d)

DATEPART(MI,d)

MINUTE(d)

MINUTE(d)

EXTRACT(MINUTE FROM d)

MINUTE(d)

EXTRACT(MINUTE FROM d)

SECOND(d)

取秒

EXTRACT(SECOND FROM d)

DATEPART(SS,d)

SECOND(d)

SECOND(d)

CAST(EXTRACT(SECOND FROM d) AS INT)

SECOND(d)

EXTRACT(SECOND FROM d)

QUARTER(d)

取季度

FLOOR((EXTRACT(MONTH FROM d)+2)/3)

DATEPART(QQ,d)

QUARTER(d)

QUARTER(d)

TD_QUARTER_OF_YEAR(d)

QUARTER(d)

EXTRACT(QUARTER FROM d)

TODAY()

今天

SYSDATE

GETDATE()
/CONVERT(DATETIME,CONVERT(CHAR, GETDATE(),101),101)//
去掉时分秒

CURRENT DATE

CURDATE()

DATE

TODAY

CURRENT_DATE

NOW()

当前时间

SYSTIMESTAMP

GETDATE()/CURRENT_TIMESTAMP

CURRENT TIMESTAMP

NOW()

CURRENT_TIMESTAMP

NOW

NOW()

ADDYEARS(d,n)

增加年数

d+NUMTOYMINTERVAL(n,'YEAR')

DATEADD(YY,n,d)

d+n YEARS

d+INTERVAL n YEAR

ADD_MONTHS(d, n*12)

DATEADD('yy', n, d)/d+n YEAR

 

ADDMONTHS(d, n)

增加月数

d+NUMTOYMINTERVAL(n,'MONTH')

DATEADD(MM,n,d)

d+n MONTHS

d+INTERVAL n MONTH

ADD_MONTHS(d, n)

DATEADD('mm', n, d)/d+n MONTH

 

ADDDAYS(d, n)

增加天数

d+NUMTODSINTERVAL(n,'DAY')

DATEADD(DD,n,d)

d+n DAYS

d+INTERVAL n DAY

d+CAST(n AS INTERVAL DAY)

DATEADD('dd', n, d)/d+n DAY

 

ADDHOURS(d, n)

增加小时数

d+NUMTODSINTERVAL(n,'HOUR')

DATEADD(HH,n,d)

d+n HOURS

d+INTERVAL n HOUR

d+CAST(n AS INTERVAL HOUR)

DATEADD('hh', n, d)/d+n HOUR

 

ADDMINUTES(d, n)

增加分钟数

d+NUMTODSINTERVAL(n,'MINUTE')

DATEADD(MI,n,d)

d+n MINUTES

d+INTERVAL n MINUTE

d+CAST(n AS INTERVAL MINUTE)

DATEADD('mi', n, d)/d+n MINUTE

 

ADDSECONDS(d, n)

增加秒数

d+NUMTODSINTERVAL(n,'SECOND')

DATEADD(SS,n,d)

d+n SECONDS

d+INTERVAL n SECOND

d+CAST(n AS INTERVAL SECOND)

DATEADD('ss', n, d)/d+n SECOND

 

DAYOFYEAR(d)

当年中第几天

TO_NUMBER(TO_CHAR(d,'DDD'))

DATEPART(DY,d)

DAYOFYEAR(d)

DAYOFYEAR(d)

TD_DAY_OF_YEAR(d)

DAYOFYEAR(d)

EXTRACT(DOY FROM d)

WEEKOFYEAR(d)

当年中第几星期

TO_NUMBER(TO_CHAR(d,'WW'))

DATEPART(WW,d)

WEEK(d)

WEEK(d)

TD_WEEK_OF_YEAR(d)

WEEK(d)

EXTRACT(WEEK FROM d)

转换函数

标准函数

含义

oracle

sql server

db2

mysql

teradata

hsql

PostgresSQL

ASCII(str)

串中最左边字符的ASCII

 

 

 

 

 

 

 

CHR(n)

整数n转换成字符

CHR(n)

CHAR(n)

CHR(n)

CHAR(n)

 

CHAR(n)

 

INT(x)

字符串或数值转成整数

CAST(x AS INT)

CAST(x AS INT)

INT(x)

CAST(x AS SIGNED)

CAST(x AS INT)

CAST(x AS INT)

 

DECIMAL(x,len,scale)

字符串或数值转成数值

CAST(x AS DECIMAL(len,scale))

CAST(x AS DECIMAL(len,scale))

DECIMAL(x,len,scale)

CAST(x AS DECIMAL(len,scale))

CAST(x AS DECIAML(len,scale))

CAST(x AS DECIAML(len,scale))

 

TIMESTAMP(str)

yyyy-mm-dd hh24:mi:ss格式串转换成日期

TO_DATE(str,'YYYY-MM-DD HH24:MI:SS')

CONVERT(DATETIME,str,120)

TO_DATE(str,'YYYY-MM-DD HH24:MI:SS')

TIMESTAMP(str)

TO_TIMESTAMP(str,'YYYY-MM-DD HH24:MI:SS')

TIMESTAMP(str)

 

NUMTOCHAR(d)

数值转成字符串

TO_CHAR(d)

CAST(d AS VARCHAR)

CHAR(d)

CAST(d AS CHAR)

TO_CHAR(d)

CONVERT(d, SQL_VARCHAR)

CAST(d AS TEXT)

DATETOCHAR(date)

将日期转化成yyyy-mm-dd hh24:mi:ss格式串

TO_CHAR(date,'YYYY-MM-DD HH24:MI:SS')

CONVERT(CHAR,date,120)

TO_CHAR(date,'YYYY-MM-DD HH:MI:SS')

DATE_FORMAT(d,
 '%Y-%m-%d %H:%i:%S)

TO_CHAR(d,'YYYY-MM-DD HH24:MI:SS')

TO_CHAR(d, 'YYYY-MM-DD HH24:MI:SS')

TO_CHAR(d,'YYYY-MM-DD HH24:MI:SS')

CAST(x, y)

x转化为数据类型y(注:y不能使用?)

CAST(x as y)

CAST(x as y)

CAST(x as y)

CAST(x as y)

CAST(x as y)

CAST(x as y)

 

NULLIF(x1,x2)

x1=x2返回null,否则返回x1

 

 

 

 

 

 

 

DATE(str)

yyyy-mm-dd
格式串转换成日期

TO_DATE(DATES,'YYYY-MM-DD')

CONVERT(varchar(100), DATES, 23)

DATE(DATES)

DATE_FORMAT(DATES,'%Y-%m-%d')

CAST(DATES AS DATE FORMAT 'YYYY-MM-DD')

 

 

任意函数

标准函数

含义

oracle

sql server

db2

mysql

teradata

hsql

PostgresSQL

CASE(when1,then1,

…[,else])

when=true则返回相应的then,否则返回else

CASE WHEN when1 THEN then1 … ELSE else END

CASE WHEN when1 THEN then1 … ELSE else END

CASE WHEN when1 THEN then1 … ELSE else END

CASE WHEN when1 THEN then1 … ELSE else END

 

CASE WHEN when1 THEN then1 … ELSE else END

 

COALESCE(x1,…)

返回第一个非null的参数

 

 

 

 

 

 

 

CONCAT(s1,s2,…)

连接多个字符串

s1||s2||…

s1+s2+…

s1||s2||…

CONCAT(s1,s2,…)

s1||s2||…

s1||s2||…

s1||s2||…

参数:

sql

sql语句。

dbtype

数据库类型。参照表dbtype直接用名字,找不到时报错。

返回值:

SQL语句

示例:

 

A

 

1

SELECT ID,WEEKOFYEAR(DATES),CUSTOMER,AREA FROM CLUE

使用标准函数:

WEEKOFYEAR(d) : 当年中的第几个星期

2

=A1.sqltranslate("ORACLE")

返回结果:

SELECT ID,TO_NUMBER(TO_CHAR(DATES,'WW')),CUSTOMER,AREA FROM CLUE

3

=A1.sqltranslate("SQLSVR")

返回结果:

SELECT ID,DATEPART(WW,DATES),CUSTOMER,AREA FROM CLUE

4

=A1.sqltranslate("DB2")

返回结果:

SELECT ID,WEEK(DATES),CUSTOMER,AREA FROM CLUE

5

=A1.sqltranslate("MYSQL")

返回结果:

SELECT ID,WEEK(DATES),CUSTOMER,AREA FROM CLUE

6

=A1.sqltranslate("HSQL")

返回结果:

SELECT ID,WEEK(DATES),CUSTOMER,AREA FROM CLUE

7

=A1.sqltranslate("TERADATA")

返回结果:

SELECT ID,TD_WEEK_OF_YEAR(DATES),CUSTOMER,AREA FROM CLUE

8

=A1.sqltranslate("POSTGRES")

返回结果:

SELECT ID,EXTRACT(WEEK FROM DATES),CUSTOMER,AREA FROM CLUE

自定义函数:

可对当前SQL标准函数进行扩散,增加新功能,实现对数据库函数的修改和增加。

只需要在function.xml文件中进行配置,该文件位于esproc-bin-xxxx.jar/com/raqsoft/dm/sql/路径下,内容格式如下:

其中,FUNCTION节点中,name属性表示标准函数名称;paramcount属性表示函数中的参数个数;value表示数据库中对应的函数语法。

INFO节点中,dbtype属性表示数据库类型;value属性表示数据库中对应的函数写法,此处value为空则默认使用FUNCTION节点中的value的值。

增加自定义函数步骤:
1
、定义函数名及其参数个数,在<FUNCTION>节点下配置nameparamcount属性值;
2
、在<INFO>子节点下设置数据库类型,dbtype对应数据库类型,value对应数据库的函数。每个FUNCTION节点下可以有多个INFO子节点。

增加数据库类型:

增数据库类型时,用户只需在function.xml中的FUNCTION节点中增加INFO节点,并在该节点中配置dbtypevalue属性,dbtype设置为数据库类型,value设置为该数据库的对应函数即可。