SqlServer基本函数用法

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Description:    按指定符号分割字符串，返回分割后指定索引的第几个元素，象数组一样方便
-- =============================================
ALTER function [dbo].[Get_StrArrayStrOfIndex]
(
  @str varchar(1024),  --要分割的字符串
  @split varchar(10),  --分隔符号
  @index int --取第几个元素
)
returns varchar(1024)
as
begin
  declare @location int
  declare @start int
  declare @next int
  declare @seed int

  set @str=ltrim(rtrim(@str))
  set @start=1
  set @next=1
  set @seed=len(@split)
 
  set @location=charindex(@split,@str)
  while @location<>0 and @index>@next
  begin
    set @start=@location+@seed
    set @location=charindex(@split,@str,@start)
    set @next=@next+1
  end
  if @location =0 select @location =len(@str)+1
 --这儿存在两种情况：
-- 1、字符串不存在分隔符号
-- 2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。
 
  return substring(@str,@start,@location-@start)
end
--调用示例：select dbo.Get_StrArrayStrOfIndex('8,9,4',',',2)
--返回值：9

 

****************

-- Description:    按指定符号分割字符串，返回分割后的元素个数，
--就是看字符串中存在多少个分隔符号，然后再加一，就是要求的结果。
-- =============================================
ALTER function [dbo].[Get_StrArrayLength]
(
  @str varchar(1024),  --要分割的字符串
  @split varchar(10)  --分隔符号
)
returns int
as
begin
  declare @location int
  declare @start int
  declare @length int

  set @str=ltrim(rtrim(@str))
  set @location=charindex(@split,@str)
  set @length=1
  while @location<>0
  begin
    set @start=@location+1
    set @location=charindex(@split,@str,@start)
    set @length=@length+1
  end
  return @length
end
--调用示例：select dbo.Get_StrArrayLength('78,1,2,3',',')
--返回值：4

*****************************

-- Description:    结合上边两个函数，象数组一样遍历字符串中的元素
-- =============================================
ALTER   function   [dbo].[f_splitstr](@SourceSql   varchar(8000),@StrSeprate   varchar(100))   
  returns   @temp   table(F1   varchar(100))   
  as     
  begin   
  declare   @ch   as   varchar(100)   
  set   @SourceSql=@SourceSql+@StrSeprate     
  while(@SourceSql<>'')   
                  begin   
                  set   @ch=left(@SourceSql,charindex(@StrSeprate, @SourceSql,1)-1)   
  insert   @temp   values(@ch)   
  set   @SourceSql=stuff(@SourceSql,1,charindex(@StrSeprate, @SourceSql,1),'')   
                  end   
  return   
  end   

----调用select   *   from   dbo.f_splitstr('1;2;3;4',';')
--结果：
--1
--2
--3
--4

**************************

分页存储过程

**************************

ALTER procedure [dbo].[Sp_DivPageBySql]
 @strSql varchar(8000),
 @nPageSize int,--每页记录数
 @nPageNo int--第x页
as
        SET NOCOUNT ON
        DECLARE @P1 INT, @nRowCount INT,@nStartRecNo Int

        --//注意:@scrollopt = 1 会取得Select的时候的总行数
        EXEC sp_cursoropen @P1 OUTPUT, @strSql, @scrollopt = 2, @ccopt = 335873, @rowcount = @nRowCount OUTPUT
        Print @nRowCount

        IF (@P1 != 0)
        BEGIN
             --SELECT @nRowCount AS nRecordCount, ceiling(1.0 * @nRowCount / @nPageSize) AS nPageCount, @nPageCount AS nPage

                IF @nPageNo = 0 BEGIN
                   SET @nStartRecNo = (ceiling(1.0 * @nRowCount / @nPageSize)-1) * @nPageSize + 1
                 END
                 ELSE BEGIN
                     SET @nStartRecNo = (@nPageNo - 1) * @nPageSize + 1
                 END

                 EXEC sp_cursorfetch @P1, 32, @nStartRecNo, @nPageSize
                 EXEC sp_cursorclose @P1
        END

--调用的方式
--表
--exec Sp_DivPageBySql 'select * from 表',10,3
--存储过程
--exec Sp_DivPageBySql 'exec 存储过程',10,1

 ***********************

返回两个日期之间的记录

select * from Calendar  where UserID=229713 AND DateDiff(day,StartTime,getdate()) >=0 AND DateDiff(day,getdate(),EndTime) <=0 order by CalendarID desc

判断字符串是否存在： charindex('a', 'abc')>0                        

数字转字符串：STR(数字,3)

日期转字符串：CONVERT(varchar(100), GETDATE(), 23)

 

 Case用法 一：

select userinfo.realname, Course.coursename, courseindexid as courseid, task.Title,
                case
                   when reportstate is null then ''未交''
                   when reportstate=0 then ''未阅''
                   when reportstate=1 then ''已阅''
               end as reportstate
                from task

 

Case用法 二：

             SELECT
                TaskID,Title,Content,Task.CourseID,Task.UserID,Task.AddTime,TaskType,EndTime,AppendixGUID,Task.Remark,TypeOfTask
             ,Course.[CourseName],[UserInfo].RealName as TeacherName,num=case when convert(varchar(8),EndTime,112)>= convert(varchar(8),getdate(),112) then 0 else 1 end FROM [Task]
             LEFT JOIN Course ON Course.CourseID = Task.CourseID
            left join UserInfo on UserInfo.UserID=Course.UserID