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''')