1 字符的连接
转换钱ORACLE:
select CONCAT('aaa','bbb') into vcTemp from dual;
转换后SQL SERVER
SELECT @vcTemp = ('aaa' + 'bbb')
2把ASCII字符向字符类型的转换
select CHR(41) into vcTemp from dual;
SELECT @vcTemp = char(41)
3返回指定一字符串在另一字符串中最先出现的位置。
instr(string1,string2)
在字符串string1中,从start开始找string2,省略start时从string1头开始找。找不到时,函数值为0。
select INSTR('aaab','b') into vcTemp from dual;
SELECT @vcTemp = sysdb.ssma_oracle.instr2_char('aaab', 'b')
4InStr 函数来查找某字符串在另一个字符串中首次出现的位置
select INSTR('baaa','b') into vcTemp from dual;
SELECT @vcTemp = sysdb.ssma_oracle.instr2_char('baaa', 'b')
5rpad(str1,int,char)
当str1的长度小于指定int数值的时候,在str1后面填充char字符到指定的长度为止
select RPAD('aaa',15,'b') into vcTemp from dual;
SELECT @vcTemp = sysdb.ssma_oracle.rpad_varchar('aaa', 15, 'b')
5rpad(str1,int,char)
当str1的长度大于指定int数值的时候,把str1从开始为止截取指定长度的字符串
select RPAD('aaa',2) into vcTemp from dual;
SELECT @vcTemp = sysdb.ssma_oracle.rpad_varchar('aaa', 2, DEFAULT)
7把一个数值转化为字符的情况的转换
select TO_CHAR(10) into vcTemp from dual;
SELECT @vcTemp = CAST(10 AS varchar(max))
8显示字符串长度的转换
select LENGTH('aaa') into nmTemp from dual;
SELECT @nmTemp = sysdb.ssma_oracle.length_char('aaa')
9把字符串的某个字符串替换为另外一个指定的字符
select REPLACE('aaab','b','c') into vcTemp from dual;
SELECT @vcTemp = replace('aaab', 'b', 'c')
10
select NVL('aaab','NULL') into vcTemp from dual;
SELECT @vcTemp = isnull('aaab', 'NULL')
11
select SYSDATE into dtTemp from dual;
SELECT @dtTemp = sysdatetime()
12
select TO_CHAR(SYSDATE,'YYYY/MM/DD') into vcTemp from dual;
SELECT @vcTemp = CONVERT(varchar(max), sysdatetime(), 111)
13
select TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) into nmTemp from dual;
SELECT @nmTemp = CAST(CONVERT(varchar(max), sysdatetime(), 112) AS float(53))
14
select ROUND(SYSDATE) into vcTemp from dual;
SELECT @vcTemp = sysdb.ssma_oracle.round_date(sysdatetime(), DEFAULT)
15
select TRUNC(SYSDATE,'YYYY') into vcTemp from dual;
SELECT @vcTemp = sysdb.ssma_oracle.trunc_date2(sysdatetime(), 'YYYY')
16
select TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM/DD')) into dtTemp from dual;
SELECT @dtTemp = CAST(CONVERT(varchar(max), sysdatetime(), 111) AS DATETIME)
17
select NVL(SYSDATE,TRUNC(SYSDATE,'YYYY')) into dtTemp from dual;
SELECT @dtTemp = isnull(sysdatetime(), sysdb.ssma_oracle.trunc_date2(sysdatetime(), 'YYYY'))
18
LAST_DAY
sysdb.ssma_oralce.last_day
19
GREATEST
sysdb.ssma_oralce.greatest_datetime
sysdb.ssma_oralce.greatest_float
sysdb.ssma_oralce.greatest_int
sysdb.ssma_oralce.greatest_nvarchar
sysdb.ssma_oralce.greatest_real
sysdb.ssma_oralce.greatest_varchar
是将一些处理过的按一定格式编排过的字符串变回数值型的格式
DECODE(value, if1, then1, if2,then2, if3,then3, . . . else )
转换钱ORACLE:
select CONCAT('aaa','bbb') into vcTemp from dual;
转换后SQL SERVER
SELECT @vcTemp = ('aaa' + 'bbb')
2把ASCII字符向字符类型的转换
select CHR(41) into vcTemp from dual;
SELECT @vcTemp = char(41)
3返回指定一字符串在另一字符串中最先出现的位置。
instr(string1,string2)
在字符串string1中,从start开始找string2,省略start时从string1头开始找。找不到时,函数值为0。
select INSTR('aaab','b') into vcTemp from dual;
SELECT @vcTemp = sysdb.ssma_oracle.instr2_char('aaab', 'b')
4InStr 函数来查找某字符串在另一个字符串中首次出现的位置
select INSTR('baaa','b') into vcTemp from dual;
SELECT @vcTemp = sysdb.ssma_oracle.instr2_char('baaa', 'b')
5rpad(str1,int,char)
当str1的长度小于指定int数值的时候,在str1后面填充char字符到指定的长度为止
select RPAD('aaa',15,'b') into vcTemp from dual;
SELECT @vcTemp = sysdb.ssma_oracle.rpad_varchar('aaa', 15, 'b')
5rpad(str1,int,char)
当str1的长度大于指定int数值的时候,把str1从开始为止截取指定长度的字符串
select RPAD('aaa',2) into vcTemp from dual;
SELECT @vcTemp = sysdb.ssma_oracle.rpad_varchar('aaa', 2, DEFAULT)
7把一个数值转化为字符的情况的转换
select TO_CHAR(10) into vcTemp from dual;
SELECT @vcTemp = CAST(10 AS varchar(max))
8显示字符串长度的转换
select LENGTH('aaa') into nmTemp from dual;
SELECT @nmTemp = sysdb.ssma_oracle.length_char('aaa')
9把字符串的某个字符串替换为另外一个指定的字符
select REPLACE('aaab','b','c') into vcTemp from dual;
SELECT @vcTemp = replace('aaab', 'b', 'c')
10
select NVL('aaab','NULL') into vcTemp from dual;
SELECT @vcTemp = isnull('aaab', 'NULL')
11
select SYSDATE into dtTemp from dual;
SELECT @dtTemp = sysdatetime()
12
select TO_CHAR(SYSDATE,'YYYY/MM/DD') into vcTemp from dual;
SELECT @vcTemp = CONVERT(varchar(max), sysdatetime(), 111)
13
select TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) into nmTemp from dual;
SELECT @nmTemp = CAST(CONVERT(varchar(max), sysdatetime(), 112) AS float(53))
14
select ROUND(SYSDATE) into vcTemp from dual;
SELECT @vcTemp = sysdb.ssma_oracle.round_date(sysdatetime(), DEFAULT)
15
select TRUNC(SYSDATE,'YYYY') into vcTemp from dual;
SELECT @vcTemp = sysdb.ssma_oracle.trunc_date2(sysdatetime(), 'YYYY')
16
select TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM/DD')) into dtTemp from dual;
SELECT @dtTemp = CAST(CONVERT(varchar(max), sysdatetime(), 111) AS DATETIME)
17
select NVL(SYSDATE,TRUNC(SYSDATE,'YYYY')) into dtTemp from dual;
SELECT @dtTemp = isnull(sysdatetime(), sysdb.ssma_oracle.trunc_date2(sysdatetime(), 'YYYY'))
18
LAST_DAY
sysdb.ssma_oralce.last_day
19
GREATEST
sysdb.ssma_oralce.greatest_datetime
sysdb.ssma_oralce.greatest_float
sysdb.ssma_oralce.greatest_int
sysdb.ssma_oralce.greatest_nvarchar
sysdb.ssma_oralce.greatest_real
sysdb.ssma_oralce.greatest_varchar
是将一些处理过的按一定格式编排过的字符串变回数值型的格式
DECODE(value, if1, then1, if2,then2, if3,then3, . . . else )