DB2一些SQL的用法

1.RRN(Table) 会显示该record在这个表中唯一的行号，此行号自动增长，不会被重用
select RRN(Table) as RowID from Table

2.Update *HIVAL/*LOCAL by SQL
Update Table Set Value=X'FFFF'    // *HIVAL
Update Table Set Value=X'0000'    // *LOVAL

3.连接字符串
Update Table Set Column1='R'||Column1
Update Table Set Column1='ABC'||SubString(Column1,4)
Update Table Set Column1='ABC'||Right(Column1,2)

4.查找Column1中的空格位置
SELECT  Locate(' ',trim(Column1)) from Table

5.截取字符串
SELECT Substr(Column1,3),Substr(Column1,3,2),Left(Column,3),Right(Column,3) from Table

6.當前日期時間
SELECT curdate(),curtime() from Table

7.日期比較
SELECT days (curdate()) - days(date('2014-02-01')) FROM Table

AS400 计算日期时间
http://www.aixchina.net/club/thread-27763-1-1.html

8.前面幾條
select top ?

SELECT * FROM orders FETCH FIRST 100 ROWS ONLY

9.字符串長度，去除空格
length(...)
trim(...)  rtrim(...)  ltrim(...)

SELECT icincd,length(name),length(trim(name)) FROM orders

10.替換字符串
select replace('aaabb','ab','vvv') from  orders

11.Case when

SELECT * from orders     
ORDER BY CASE no WHEN 0 THEN 900000 ELSE no END

12.轉義字符
2個引號
INSERT INTO QTEMP/TEST VALUES(1,'abc''')