sql的标量值函数例子

1.分割字符串,处理某个字符第几次出现获取后面的值

USE [EF]
GO
/****** Object:  UserDefinedFunction [dbo].[f_subStr]    Script Date: 10/27/2014 20:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER  FUNCTION [dbo].[f_subStr](
@s     varchar(8000),  --待分拆的字符串
@split  varchar(10),     --数据分隔符
@fx  varchar(10),		--方向 left
@place  int				---第几个
)RETURNS varchar(8000)
AS
begin
	declare @str varchar(8000)
	declare @i int		---循环次数
	declare @pp int		---位置
	set @pp=0
	set @i=@place
	set @str=@s
	while(@i>0)
	begin
		declare @p int
		set @p=charindex(@split,@str)
		if(@p>0)
		begin
			set @str = substring(@str,@p+1,len(@str)-@p+1)
		end
		else
		begin
			return ''
		end
		set @pp = @pp+@p
		set @i = @i-1
	end
	if(@fx='left')
	begin
		set @str=left(@s,@pp-1)
	end

	RETURN @str
end

2. 获取某天是当月第几周

USE [EF]
GO
/****** Object:  UserDefinedFunction [dbo].[WeekOfMonth]    Script Date: 10/27/2014 20:51:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[WeekOfMonth](@day datetime)  
RETURNS int
AS
begin

declare @num int
declare @Start datetime
declare @dd int
declare @dayofweek char(8)
declare @dayofweek_num char(8)
declare @startWeekDays int

if datepart(dd,@day)=1
return 1
else
set @Start= (SELECT   DATEADD(mm,   DATEDIFF(mm,0,@day),   0)) --一个月第一天的
set @dayofweek= (datename(weekday,@Start)) ---得到本月第一天是周几
set @dayofweek_num=(select (case @dayofweek when '星期一' then 7
                            when '星期二' then 6
                            when '星期三' then 5
                            when '星期四' then 4
                            when '星期五' then 3
                            when '星期六' then 2
                            when '星期日' then 1
                            end))  ---得到本月的第一周一共有几天
set @dd=datepart(dd,@day) ----得到今天是这个月的第几天
if @dd <= @dayofweek_num  --小于前一周的天数
return 1
else
set @dd=@dd-@dayofweek_num
if @dd % 7=0
     begin
       set @num=@dd / 7
        return @num+1      
     end
else --if @dd % 7<>0
  
set @num=@dd / 7
set @num=@num+1+1
       return @num
end

3. 获取客户端IP

/****** Object:  UserDefinedFunction [dbo].[GetCurrentIP]    Script Date: 10/27/2014 20:53:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[GetCurrentIP] ()
RETURNS varchar(255)
AS
BEGIN
	DECLARE @IP_Address varchar(255);
    SELECT @IP_Address = client_net_address
    FROM sys.dm_exec_connections
    WHERE Session_id = @@SPID;
	Return @IP_Address;
END

4.输入一个日期获取下个月是几月

USE [EF]
GO
/****** Object:  UserDefinedFunction [dbo].[f_GetNextperiod]    Script Date: 10/27/2014 20:55:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[f_GetNextperiod]
(
@QJ VARCHAR(30)
)
RETURNS VARCHAR(20)
AS
BEGIN

     DECLARE @DAY VARCHAR(12)
     DECLARE @Year VARCHAR(4)
     DECLARE @Month VARCHAR(2)

     SET @DAY = dbo.Get_StrArrayStrOfIndex(@QJ,'.',1)+'-'+dbo.Get_StrArrayStrOfIndex(@QJ,'.',2)+'-01'

		SET  @Month =CONVERT(VARCHAR(2),MONTH(DATEADD(MONTH,+1,@DAY))) 
		SET  @Year  = CONVERT(VARCHAR(4),YEAR(DATEADD(MONTH,+1,@DAY)))

	-- Return the result of the function
	RETURN @Year+'.'+@Month
END

SELECT dbo.f_GetNextperiod('2014.10.27')
/*结果
2014.11
*/