/*
********************************
* 获取日期的完整字符串形式
* Ridge Wong @ 2008-7-9
********************************** */
ALTER function [ dbo ] . [ GetDateFullString ] (
@date datetime
)
returns varchar ( 10 )
as
begin
return convert ( varchar ( 4 ), Year ( @date ))
+ ( case when Month ( @date ) > 9 then ' - ' + convert ( varchar ( 2 ), Month ( @date )) else Left ( ' -0 ' + convert ( varchar ( 2 ), Month ( @date )), 3 ) end )
+ ( case when Day ( @date ) > 9 then ' - ' + convert ( varchar ( 2 ), Day ( @date )) else Left ( ' -0 ' + convert ( varchar ( 2 ), Day ( @date )), 3 ) end )
end
* 获取日期的完整字符串形式
* Ridge Wong @ 2008-7-9
********************************** */
ALTER function [ dbo ] . [ GetDateFullString ] (
@date datetime
)
returns varchar ( 10 )
as
begin
return convert ( varchar ( 4 ), Year ( @date ))
+ ( case when Month ( @date ) > 9 then ' - ' + convert ( varchar ( 2 ), Month ( @date )) else Left ( ' -0 ' + convert ( varchar ( 2 ), Month ( @date )), 3 ) end )
+ ( case when Day ( @date ) > 9 then ' - ' + convert ( varchar ( 2 ), Day ( @date )) else Left ( ' -0 ' + convert ( varchar ( 2 ), Day ( @date )), 3 ) end )
end
SQL三元操作符号 ?:
/*
****************************************************************************
* Name: fn_GetBitString
* Author: Ridge Wong
* Create Date: 2005年12月13日 15:28
* Version: V1.0.00
* Function: simulate ?: Operation in C/C++ style Language
* Example: fn_GetBitString(IsDelete,'已删除','未删除')
**************************************************************************** */
ALTER FUNCTION [ dbo ] . [ fn_GetBitString ] (
@bitField bit ,
@strTrueReturn nvarchar ( 2000 ),
@strFalseReturn nvarchar ( 2000 )
)
RETURNS nvarchar ( 4000 )
AS
BEGIN
declare @strReturn nvarchar ( 2000 )
if @bitField is null
set @strReturn = @strFalseReturn
else
begin
if ( @bitField = 1 )
set @strReturn = @strTrueReturn
else
set @strReturn = @strFalseReturn
end
return ( @strReturn )
END
* Name: fn_GetBitString
* Author: Ridge Wong
* Create Date: 2005年12月13日 15:28
* Version: V1.0.00
* Function: simulate ?: Operation in C/C++ style Language
* Example: fn_GetBitString(IsDelete,'已删除','未删除')
**************************************************************************** */
ALTER FUNCTION [ dbo ] . [ fn_GetBitString ] (
@bitField bit ,
@strTrueReturn nvarchar ( 2000 ),
@strFalseReturn nvarchar ( 2000 )
)
RETURNS nvarchar ( 4000 )
AS
BEGIN
declare @strReturn nvarchar ( 2000 )
if @bitField is null
set @strReturn = @strFalseReturn
else
begin
if ( @bitField = 1 )
set @strReturn = @strTrueReturn
else
set @strReturn = @strFalseReturn
end
return ( @strReturn )
END
获取字符分隔数组项
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1
/*
****************************************************************************
2 * Name: fn_GetArrayItem
3 * Author: Ridge Wong
4 * Create Date: 2005年11月2日 15:57:34
5 * Version: V1.0.00
6 * Example: fn_GetArrayItem('32|4|11|43',2,'|') = 11
7 **************************************************************************** */
8 ALTER FUNCTION [ dbo ] . [ fn_GetArrayItem ] (
9 @strGroup varchar ( 8000 ),
10 @idx int ,
11 @strSperator varchar ( 200 ) = ' | '
12 )
13 RETURNS varchar ( 1000 )
14 AS
15
16 BEGIN
17
18 declare @idxItem int , @idxStart int , @idxEnd int
19 declare @strResult varchar ( 1000 )
20
21 set @idxItem = - 1
22 set @idxEnd = CHARINDEX ( @strSperator , @strGroup , 0 )
23 set @idxStart = 0
24
25 if @idxEnd > 0
26 begin
27 WHILE ( @idxItem < @idx )
28 BEGIN
29 set @idxItem = @idxItem + 1
30 if ( @idxEnd > 0 )
31 begin
32 if ( @idxItem = @idx )
33 set @strResult = SUBSTRING ( @strGroup , @idxStart , @idxEnd - @idxStart )
34 -- update flag
35 set @idxStart = @idxEnd + len ( @strSperator )
36 set @idxEnd = CHARINDEX ( @strSperator , @strGroup , @idxStart )
37 end
38 else
39 set @strResult = SUBSTRING ( @strGroup , @idxStart , len ( @strGroup ) - @idxStart + 1 )
40 END
41 end
42 else
43 set @strResult = @strGroup
44
45 RETURN ( @strResult )
46
47 END
48
49
2 * Name: fn_GetArrayItem
3 * Author: Ridge Wong
4 * Create Date: 2005年11月2日 15:57:34
5 * Version: V1.0.00
6 * Example: fn_GetArrayItem('32|4|11|43',2,'|') = 11
7 **************************************************************************** */
8 ALTER FUNCTION [ dbo ] . [ fn_GetArrayItem ] (
9 @strGroup varchar ( 8000 ),
10 @idx int ,
11 @strSperator varchar ( 200 ) = ' | '
12 )
13 RETURNS varchar ( 1000 )
14 AS
15
16 BEGIN
17
18 declare @idxItem int , @idxStart int , @idxEnd int
19 declare @strResult varchar ( 1000 )
20
21 set @idxItem = - 1
22 set @idxEnd = CHARINDEX ( @strSperator , @strGroup , 0 )
23 set @idxStart = 0
24
25 if @idxEnd > 0
26 begin
27 WHILE ( @idxItem < @idx )
28 BEGIN
29 set @idxItem = @idxItem + 1
30 if ( @idxEnd > 0 )
31 begin
32 if ( @idxItem = @idx )
33 set @strResult = SUBSTRING ( @strGroup , @idxStart , @idxEnd - @idxStart )
34 -- update flag
35 set @idxStart = @idxEnd + len ( @strSperator )
36 set @idxEnd = CHARINDEX ( @strSperator , @strGroup , @idxStart )
37 end
38 else
39 set @strResult = SUBSTRING ( @strGroup , @idxStart , len ( @strGroup ) - @idxStart + 1 )
40 END
41 end
42 else
43 set @strResult = @strGroup
44
45 RETURN ( @strResult )
46
47 END
48
49
获取字符分隔数组的长度
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1
/*
****************************************************************************
2 * Name: fn_GetArrayItemCount
3 * Author: Ridge Wong
4 * Create Date: 2005年11月10日 10:35:53
5 * Version: V1.0.00
6 * Example: fn_GetArrayItemCount('32|4|11|43','|') = 4
7 **************************************************************************** */
8 ALTER FUNCTION [ dbo ] . [ fn_GetArrayItemCount ] (
9 @strGroup varchar ( 8000 ),
10 @strSperator varchar ( 200 ) = ' | '
11 )
12 RETURNS int
13 AS
14
15 BEGIN
16
17 declare @idxStart int , @idxEnd int , @ItemCount int
18
19 set @ItemCount = 1
20 set @idxStart = 0
21 set @idxEnd = CHARINDEX ( @strSperator , @strGroup , @idxStart )
22
23 WHILE ( @idxEnd > 0 )
24 BEGIN
25 set @ItemCount = @ItemCount + 1
26 set @idxStart = @idxEnd + len ( @strSperator )
27 set @idxEnd = CHARINDEX ( @strSperator , @strGroup , @idxStart )
28 END
29
30 RETURN ( @ItemCount )
31
32 END
33
2 * Name: fn_GetArrayItemCount
3 * Author: Ridge Wong
4 * Create Date: 2005年11月10日 10:35:53
5 * Version: V1.0.00
6 * Example: fn_GetArrayItemCount('32|4|11|43','|') = 4
7 **************************************************************************** */
8 ALTER FUNCTION [ dbo ] . [ fn_GetArrayItemCount ] (
9 @strGroup varchar ( 8000 ),
10 @strSperator varchar ( 200 ) = ' | '
11 )
12 RETURNS int
13 AS
14
15 BEGIN
16
17 declare @idxStart int , @idxEnd int , @ItemCount int
18
19 set @ItemCount = 1
20 set @idxStart = 0
21 set @idxEnd = CHARINDEX ( @strSperator , @strGroup , @idxStart )
22
23 WHILE ( @idxEnd > 0 )
24 BEGIN
25 set @ItemCount = @ItemCount + 1
26 set @idxStart = @idxEnd + len ( @strSperator )
27 set @idxEnd = CHARINDEX ( @strSperator , @strGroup , @idxStart )
28 END
29
30 RETURN ( @ItemCount )
31
32 END
33