DB2 SQL函数

最新推荐文章于 2022-05-18 08:58:21 发布

pianzif

最新推荐文章于 2022-05-18 08:58:21 发布

阅读量4k

点赞数

分类专栏： DB2@BK 文章标签： db2 sql 函数

DB2@BK 专栏收录该内容

104 篇文章 4 订阅

订阅专栏

1.DB2行列转换

行转列
给出下面的数据:

CREATE TABLE Sales (Year INT, Quarter INT, Results INT)
YEAR        QUARTER     RESULTS
----------- ----------- -----------
       2004           1          20
       2004           2          30
       2004           3          15
       2004           4          10
       2005           1          18
       2005           2          40
       2005           3          12
       2005           4          27
想要的到结果:
YEAR        Q1          Q2          Q3          Q4
----------- ----------- ----------- ----------- -----------
       2004          20          30          15          10
       2005          18          40          12          27
这个SQL就可解决这个问题:
SELECT Year,
       MAX(CASE WHEN Quarter = 1
           THEN Results END) AS Q1,
       MAX(CASE WHEN Quarter = 2
           THEN Results END) AS Q2,
       MAX(CASE WHEN Quarter = 3
           THEN Results END) AS Q3,
       MAX(CASE WHEN Quarter = 4
           THEN Results END) AS Q4
FROM Sales
GROUP BY Year

解释一下为什么要加max的原因，因为不加max的话结果会是这样：YEAR        Q1          Q2          Q3          Q4
----------- ----------- ----------- ----------- -----------
       2004          20           -           -           -
       2004           -          30           -           -
       2004           -           -          15           -
       2004           -           -           -          10
       2005          18           -           -           -
       2005           -          40           -           -
       2005           -           -          12           -
       2005           -           -           -          27

列转行

给出下面数据
CREATE TABLE SalesAgg
(  year INTEGER,
   q1 INTEGER,
   q2 INTEGER,
   q3 INTEGER,
   q4 INTEGER );

YEAR        Q1          Q2          Q3          Q4
----------- ----------- ----------- ----------- -----------
       2004          20          30          15          10
       2005          18          40          12          27

想要的结果
YEAR        QUARTER     RESULTS
----------- ----------- -----------
       2004           1          20
       2004           2          30
       2004           3          15
       2004           4          10
       2005           1          18
       2005           2          40
       2005           3          12
       2005           4          27

这个SQL就可以实现：

SELECT S.Year, Q.Quarter, Q.Results
FROM SalesAgg AS S,
     TABLE (VALUES(1, S.q1),
                  (2, S.q2),
                  (3, S.q3),
                  (4, S.q4))
            AS Q(Quarter, Results);

下面解释一下执行的过程：
核心是用table函数创建了一个表，这个表是用value实现的多行表，value实现虚表的例子：

db2 => select * from (values (1,2),(2,3)) as t1(col1,col2)

COL1        COL2
----------- -----------
          1           2
          2           3

  2 条记录已选择。

db2 => select * from (values 1) as a

1
-----------
1

1 条记录已选择。

所不同的是这里跟from子句中的一个表产生了关系，取出了表中的一列作为数据.

2.db2中的coalesce函数

此函数相当于Oracle中的NVL且更方便,它完全可以取代SQL中的CASE.

用法:

COALESCE(A,B,C,D...);

说明:
若A为空;则取B,若B为空,则取C;...;依此类推.即返回其参数中第一个非空表达式。

　　语法

　　COALESCE ( expression [ ,...n ] )

　　参数

　　expression

　　任何类型的表达式。

　　表示可以指定多个表达式的占位符。所有表达式必须是相同类型，或者可以隐性转换为相同的类型。

　　返回类型

　　将相同的值作为 expression 返回。

注释

　　如果所有自变量均为 NULL，则 COALESCE 返回 NULL 值。

　　COALESCE(expression1,...n) 与此 CASE 函数等价：

　　CASE

　　WHEN (expression1 IS NOT NULL) THEN expression1

　　...

　　WHEN (expressionN IS NOT NULL) THEN expressionN

　　ELSE NULL

示例

　　在下面的示例中，显示包含三列有关某个雇员每年工资收入信息的 wages 表：hourly_wage、salary 和 commission。但是，每个雇员只能接受一种付款方式。若要确定支付给所有雇员的工资总额，请使用 COALESCE 函数接受在 hourly_wage、salary 和 commission 中找到的非空值。

SET NOCOUNT ON
　　GO
　　USE master
　　IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
　　WHERE TABLE_NAME = 'wages')
　　DROP TABLE wages
　　GO
　　CREATE TABLE wages
　　(
　　emp_id tinyint identity,
　　hourly_wage decimal NULL,
　　salary decimal NULL,
　　commission decimal NULL,
　　num_sales tinyint NULL
　　)
　　GO
　　INSERT wages VALUES(10.00, NULL, NULL, NULL)
　　INSERT wages VALUES(20.00, NULL, NULL, NULL)
　　INSERT wages VALUES(30.00, NULL, NULL, NULL)
　　INSERT wages VALUES(40.00, NULL, NULL, NULL)
　　INSERT wages VALUES(NULL, 10000.00, NULL, NULL)
　　INSERT wages VALUES(NULL, 20000.00, NULL, NULL)
　　INSERT wages VALUES(NULL, 30000.00, NULL, NULL)
　　INSERT wages VALUES(NULL, 40000.00, NULL, NULL)
　　INSERT wages VALUES(NULL, NULL, 15000, 3)
　　INSERT wages VALUES(NULL, NULL, 25000, 2)
　　INSERT wages VALUES(NULL, NULL, 20000, 6)
　　INSERT wages VALUES(NULL, NULL, 14000, 4)
　　GO
　　SET NOCOUNT OFF
　　GO
　　SELECT CAST(COALESCE(hourly_wage * 40 * 52,
　　salary,
　　commission * num_sales) AS money) AS 'Total Salary'
　　FROM wages
　　GO
　　下面是结果集：
　　Total Salary
　　------------
　　20800.0000
　　41600.0000
　　62400.0000
　　83200.0000
　　10000.0000
　　20000.0000
　　30000.0000
　　40000.0000
　　45000.0000
　　50000.0000
　　120000.0000
　　56000.0000

3.ASCII()

返回字符表达式最左端字符的ASCII 码值。在ASCII（）函数中，纯数字的字符串可不用‘’括起来，但含其它字符的字符串必须用‘’括起来使用，否则会出错。

4.CHAR()

将ASCII 码转换为字符。如果没有输入0 ~ 255 之间的ASCII 码值，CHAR（）返回NULL 。

5.LOWER()和UPPER()

LOWER()将字符串全部转为小写；UPPER()将字符串全部转为大写。

6.STR()

把数值型数据转换为字符型数据。 STR (<float_expression>[，length[， <decimal>]]) length 指定返回的字符串的长度，decimal 指定返回的小数位数。如果没有指定长度，缺省的length 值为10， decimal 缺省值为0。当length 或者decimal 为负值时，返回NULL；当length 小于小数点左边（包括符号位）的位数时，返回length 个*；先服从length ，再取decimal ；当返回的字符串位数小于length ，左边补足空格。

7.去空格函数

1、LTRIM() 把字符串头部的空格去掉。

2、RTRIM() 把字符串尾部的空格去掉。

8.取子串函数

1、left()

LEFT (<character_expression>， <integer_expression>) 返回character_expression 左起 integer_expression 个字符。

2、RIGHT()

RIGHT (<character_expression>， <integer_expression>) 返回character_expression 右起 integer_expression 个字符。

3、SUBSTRING()

SUBSTRING (<expression>， <starting_ position>， length) 返回从字符串左边第starting_ position 个字符起length个字符的部分。

9.字符串比较函数

1、CHARINDEX()

返回字符串中某个指定的子串出现的开始位置。 CHARINDEX (<’substring_expression’>， <expression>) 其中substring _expression 是所要查找的字符表达式，expression 可为字符串也可为列名表达式。如果没有发现子串，则返回0 值。此函数不能用于TEXT 和IMAGE 数据类型。

2、PATINDEX()

返回字符串中某个指定的子串出现的开始位置。 PATINDEX (<’%substring _expression%’>， <column_ name>)其中子串表达式前后必须有百分号“%”否则返回值为0。与CHARINDEX 函数不同的是，PATINDEX函数的子串中可以使用通配符，且此函数可用于CHAR、 VARCHAR 和TEXT 数据类型。

10.字符串操作函数

1、QUOTENAME()

返回被特定字符括起来的字符串。 QUOTENAME (<’character_expression’>[， quote_ character]) 其中quote_ character 标明括字符串所用的字符，缺省值为“[]”。

2、REPLICATE()

返回一个重复character_expression 指定次数的字符串。 REPLICATE (character_expression integer_expression) 如果integer_expression 值为负值，则返回NULL 。

3、REVERSE()

将指定的字符串的字符排列顺序颠倒。 REVERSE (<character_expression>) 其中character_expression 可以是字符串、常数或一个列的值。

4、REPLACE()

返回被替换了指定子串的字符串。 REPLACE (<string_expression1>， <string_expression2>， <string_expression3>) 用string_expression3 替换在string_expression1 中的子串string_expression2。

4、SPACE()

返回一个有指定长度的空白字符串。 SPACE (<integer_expression>) 如果integer_expression 值为负值，则返回NULL 。

5、STUFF()

用另一子串替换字符串指定位置、长度的子串。 STUFF (<character_expression1>， <start_ position>， <length>，<character_expression2>) 如果起始位置为负或长度值为负，或者起始位置大于character_expression1 的长度，则返回NULL 值。如果length 长度大于character_expression1 中 start_ position 以右的长度，则character_expression1 只保留首字符。

11.数据类型转换函数

1、CAST()

CAST (<expression> AS <data_ type>[ length ])

2、CONVERT()

CONVERT (<data_ type>[ length ]， <expression> [， style])

1）data_type为SQL Server系统定义的数据类型，用户自定义的数据类型不能在此使用。

2）length用于指定数据的长度，缺省值为30。

3）把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT这样的INTEGER类型、结果必须是带正号或负号的数值。

4）TEXT类型到CHAR或VARCHAR类型转换最多为8000个字符，即CHAR或VARCHAR数据类型是最大长度。

5）IMAGE类型存储的数据转换到BINARY或VARBINARY类型，最多为8000个字符。

6）把整数值转换为MONEY或SMALLMONEY类型，按定义的国家的货币单位来处理，如人民币、美元、英镑等。

7）BIT类型的转换把非零值转换为1，并仍以BIT类型存储。

8）试图转换到不同长度的数据类型，会截短转换值并在转换值后显示“+”，以标识发生了这种截断。

9）用CONVERT（）函数的style 选项能以不同的格式显示日期和时间。style 是将DATATIME 和SMALLDATETIME 数据转换为字符串时所选用的由SQL Server 系统提供的转换样式编号，不同的样式编号有不同的输出格式。

12.日期函数

1、day(date_expression)

返回date_expression中的日期值

2、month(date_expression)

返回date_expression中的月份值3、year(date_expression)返回date_expression中的年份值

4、DATEADD()

DATEADD (<datepart>， <number>， <date>) 返回指定日期date 加上指定的额外日期间隔number 产生的新日期。

5、DATEDIFF()

DATEDIFF (<datepart>， <date1>， <date2>) 返回两个指定日期在datepart 方面的不同之处，即date2 超过date1的差距值，其结果值是一个带有正负号的整数值。

6、DATENAME()

DATENAME (<datepart>， <date>)以字符串的形式返回日期的指定部分此部分。由datepart 来指定。

7、DATEPART()

DATEPART (<datepart>， <date>) 以整数值的形式返回日期的指定部分。此部分由datepart 来指定。DATEPART (dd， date) 等同于DAY (date) DATEPART (mm， date) 等同于MONTH (date)DATEPART (yy， date) 等同于YEAR (date)

8、GETDATE()

以DATETIME 的缺省格式返回系统当前的日期和时间

9、

Oracle数据类型改变函数：to_char()、to_date()、to_number()等；

如果仅仅取年，月，日等，可以用to_char(sysdate, 'YYYY'),to_char('MM') ,to_char('DD')取得。

只取年月日TRUNC(SYSDATE)，取时分秒TO_CHAR(SYSDATE,'HH24:MI:SS')。

DB2数据类型改变函数：char()、varchar()、int()、date()、time()等；

取得年，月，日等的写法：YEAR(current timestamp)，MONTH(current timestamp)，DAY(current timestamp)，HOUR(current timestamp)，MINUTE(current timestamp)，SECOND(current timestamp)，MICROSECOND(current timestamp)；

只取年月日可以用DATE(current timestamp)，取时分秒TIME(current timestamp)。

Char()是定长字符串（1-255），varchar()为非定长字符串（1-32672）日期,时间形态变为字符形态: char(current date)，char(current time)

将字符串转换成日期或时间形态:TIMESTAMP('2002-10-2012:00:00'),DATE('2002-10-20'),DATE('10/20/2002'),TIME('12:00:00')

目前DB2 V8也支持to_char和to_date

10.

输入参数为字符类型，且允许为空的，可以使用COALESCE(inputParameter,'')把NULL转换成'';
输入类型为整型，且允许为空的，可以使用COALESCE(inputParameter,0)，把空转换成0；
输入参数为字符类型，且是非空非空格的，可以使用COALESCE(inputParameter,'')把NULL转换成''，然后判断函数返回值是否为''；
输入类型为整型，且是非空的，不需要使用COALESCE函数，直接使用IS NULL进行非空判断。
对''值的处理，' '或''在数据库中不是NULL而是空格存在需要使用 = ' '验证

11.VALUE函数

语法：VALUE(EXPRESSION1,EXPRESSION2)

VALUE函数是用返回一个非空的值，当其第一个参数非空，直接返回该参数的值，如果第一个参数为空，则返回第二个参数的值。

 
      eg:  
 --表示如果T1.ID为空，则返回空串，如果T1.ID不为空，则返回T1.ID。  
 SELECT VALUE(ID,'') FROM T1

12.LENGTH函数

语法：LENGTH(ARG)

LENGTH函数返回参数的长度。

     Sql代码   
     
   
 eg:  
 SELECT LENGTH(NAME) FROM T1  

13.LCASE、LOWER函数

语法：LCASE()、LOWER()

LCASE、LOWER函数返回定长、变长字符串的小写形式。

     Sql代码   
     
   
 eg:  
 SELECT LCASE(NAME),LOWER(NAME) FROM T1  

14.UCASE、UPPER函数

语法：UCASE()、UPPER()

UCASE、UPPER函数返回定长、变长字符串的大写形式。

     Sql代码   
     
   
 eg:  
 SELECT UCASE(NAME),UPPER(NAME) FROM T1  

15：LTRIM、RTRIM函数

语法：LTRIM()、RTRIM()

LTRIM、RTRIM函数从CHAR、VARCHAR、GRAPHIC或者VARGRAPHIC中去掉左侧或右侧的空格。

     Sql代码   
     
   
 eg:  
 SELECT LTRIM(NAME),RTRIM(NAME) FROM T1  

16.LEFT、RIGHT函数

语法：LEFT(ARG,LENGTH)、RIGHT(ARG,LENGTH)

LEFT、RIGHT函数返回ARG最左边、右边的LENGTH个字符串，ARG可以是CHAR或BINARY STRING。

     Sql代码   
     
   
 eg:  
 SELECT LEFT(NAME,2),RIGHT(NAME,2) FROM T1  

17.INSERT函数

语法：INSERT(ARG1,POS,SIZE,ARG2)

INSERT函数返回一个字符串，将ARG1从POS处删除SIZE个字符，将ARG2插入该位置。

18.LOCATE函数

语法：LOCATE(ARG1,ARG2,<POS>)

LOCATE函数在ARG2中查找ARG1第一次出现的位置，如果指定POS，则从ARG2的POS处开始查找ARG1第一次出现的位置。

     Sql代码   
     
   
 eg:  
 SELECT LOCATE('a',NAME) FROM T1  

19.POSSTR函数

语法：POSSTR(EXP1,EXP2)

POSSTR函数返回EXP2在EXP1中的位置。

     Sql代码   
     
   
 eg:  
 SELECT LOCATE(NAME，'a') FROM T1  

20.REPEAT函数

语法：REPEAT(ARG1,NUM_TIMES)

REPEAT函数返回ARG1被重复NUM_TIMES次的字符串。

     Sql代码   
     
   
 eg:  
 SELECT REPEAT(NAME，2) FROM T1  

21.REPLACE函数

语法：REPLACE(EXP1,EXP2,EXP3)

REPLACE函数用EXP3代替EXP1中所有的EXP2。

     Sql代码   
     
   
 eg:  
 SELECT REPLACE('ROMANND'，'NND','CCB') FROM T1  

22.SPACE函数

语法：SPACE(SIZE)

SPACE函数返回一个包含SIZE个空格的字符串。

     Sql代码   
     
   
 eg:  
 SELECT SPACE(10) FROM T1  

23.SUBSTR函数

语法：SUBSTR(ARG1,POS,<LENGTH>)

SUBSTR函数返回ARG1中POS位置开始的LENGTH个字符，如果没有指定LENGTH，则返回剩余的字符。

     Sql代码   
     
   
 eg:  
 SELECT SUBSTR('CDNJFDJFJD',5,2) FROM T1  

http://jacki6.iteye.com/blog/501224

http://jdw.iteye.com/blog/993044

http://www.cnblogs.com/Fskjb/category/198702.html

pianzif

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
DB2 SQL函数

1.DB2行列转换行转列给出下面的数据:CREATE TABLE Sales (Year INT, Quarter INT, Results INT)YEAR QUARTER RESULTS----------- ----------- ----------- 2004 1 20 2004
复制链接

扫一扫