SQL 常用函数

强制类型转换
字符串重复指定次数
截取字符串
GUID
ISNULL() 函数处理空值
NULLIF() 函数处理空值
DATEPART(dw,DATE) 日期转换
XML
行转列
设置日期默认读取语言
FOR XML PATH('')

1、强制类型转换

1 cast(column_name as varchar2(20))
2 CONVERT(VARCHAR(19),GETDATE())
3 CONVERT(VARCHAR(10),GETDATE(),110) 
4 
5 --Dec 29 2008 11:45 PM
6 --12-29-2008

View Code

2、字符串值重复指定的次数

1 REPLICATE ( string_expression ,integer_expression )
2 
3 --string_expression  字符串
4 --integer_expression  重复的次数

View Code

3、截取字符串

1 --left 从左边起截取N个字符
2  left('123456',3)
3 
4 --效果：123
5 
6 --right 从右边起截取N个字符
7   right('123456',3)
8 
9 --效果：456

View Code

4、GUID

1 newid()
2 
3 replace(ltrim(newid()),'-','')

View Code

5、使用ISNULL()函数来处理空值

 1 --ISNULL()函数的是用指定的值替换空值。
 2 --语法：
 3 ISNULL (check_expression , replacement_value )
 4 
 5 declare @xx varchar(32)
 6 set @xx = null
 7 print ISNULL(@xx,'123')
 8 
 9 
10 --输出 123

View Code

6、使用NULLIF()函数来处理空值

1 --NULLIF()函数功能：如果一个数据表中应该使用NULL 值的地方使用了其他数据，那么就可以使用NULLIF()函数将这些不正确的值替换为空值。
2 --语法：
3 NULLIF ( expression , expression )
4 
5 declare @xx varchar(32)
6 set @xx = '123'
7 print nullif(@xx,'123')
8 
9 --输出 null

View Code

7、DATEPART(dw,DATE) 日期转换

1 --该日期对应周日
2 print DATEPART(dw,'2013-10-26')
3 
4 --输出 7
5 --如果是周六 输出1 
6 --周一 输出2
7 --周二 输出3
8 --...

View Code

8、XML

 1 select 
 2     b.Col2
 3 from 
 4     (select COl2=convert(xml,'<root><v>'+replace(Department,',','</v><v>')+'</v></root>') from ERPUser where UserName='test2')a
 5 outer apply
 6     (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b
 7 
 8 
 9 
10 --例 
11 --Department 
12 -- 11,12
13 
14 --执行后结果
15 --COL2
16 --11
17 --12

View Code

9、行转列

 1 if object_id('tb')is not null drop table tb
 2 
 3 go
 4 
 5 create table tb(姓名 varchar(10),课程 varchar(10),分数 int)
 6 
 7 insert into tb values('张三','语文',74)
 8 
 9 insert into tb values('张三','数学',83)
10 
11 insert into tb values('张三','物理',93)
12 
13 insert into tb values('李四','语文',74)
14 
15 insert into tb values('李四','数学',84)
16 
17 insert into tb values('李四','物理',94)
18 
19 go
20 
21 select*from tb
22 
23 go
24 
25 --姓名  课程 分数
26 --张三  语文  74
27 --张三  数学  83
28 --张三  物理  93
29 --李四  语文  74
30 --李四  数学  84
31 --李四  物理  94        
32 
33 select* from tb pivot(max(分数)for 课程in(语文,数学,物理))a
34 
35 --或者
36 select 姓名,
37 
38  max(case课程when'语文'then分数else0end)语文,
39 
40  max(case课程when'数学'then分数else0end)数学,
41 
42  max(case课程when'物理'then分数else0end)物理
43 
44 fromtb
45 
46 groupby姓名
47 
48 --姓名  语文  数学  物理
49 --李四   74       84       94
50 --张三   74   83       93

View Code

10、设置日期默认读取语言

 1 --只针对单个数据库
 2 
 3 SET LANGUAGE 简体中文
 4 GO
 5 
 6 --may 12 2012
 7 
 8 --执行后
 9 
10 --04 12 2012

View Code

11 、多行合并 FOR XML PATH('')

--创建 test 表 ，插入数据
 
CREATE TABLE test(code varchar(50), [values] varchar(10),[count] int)
INSERT test SELECT '001', 'aa',1
UNION ALL SELECT '001', 'bb',2
UNION ALL SELECT '002', 'aaa',4
UNION ALL SELECT '002', 'bbb',5
UNION ALL SELECT '002', 'ccc',3;


SELECT code, data=STUFF((SELECT ','+[values] FROM test t WHERE code=t1.code FOR XML PATH('')), 1, 1, ''),sum([count]) as [count]
FROM test t1
GROUP BY code


--查询结果
 
--001    aa,bb    3
--002    aaa,bbb,ccc    12

View Code