oracle数据库向sql server转换

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 )