SQLServer字符串操作函数

/*********************************
* 获取日期的完整字符串形式
* 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

获取字符分隔数组项

 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 

获取字符分隔数组的长度

 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 

相关附件