SQL常用的函数
来源：互联网 发布：淘宝卖家信誉度怎么看 编辑：程序博客网 时间：2024/06/14 02:37
SQL Server提供了大量的函数,
但是在一些常见的如,
字符串拆分,
字符提取,过滤等没有对应的处理,
本帖主要收集一些常见的函数,
整理如下:
------------------------------
http://topic.csdn.net/u/20080306/23/d3c100f2-cda1-4efa-927d-f1f7968884ce.html
/*
功能:拆分字符串.
作者:.....
*/
http://topic.csdn.net/u/20080724/11/dacb530f-62ba-4417-a11a-fe2dee2172b4.html
/*
功能:根据身份证号码取得此CID所在省份
作者:happyflystone
*/
http://topic.csdn.net/u/20080713/00/77925c47-b7fa-4c1b-b307-0328e74a1c09.html
/*
功能:提取数字
功能:提取英文
功能:提取中文
功能:过滤重复字符
功能:过滤重复字符2
作者:wzy_love_sly
*/
MS SQL Server vs Oracle函数
http://topic.csdn.net/u/20071022/09/477d57b8-1127-4159-8975-2055343c624f.html
sql 函数 md5
http://topic.csdn.net/u/20080619/09/4fb22d1b-f88e-487e-8b74-cf394263fc40.html
求sql取字符串的首字母的函数
http://topic.csdn.net/u/20080623/11/22a3f568-d37e-4bf5-9c13-64171066f582.html
形如1,2,3,4,5,6的字符串转换成行
http://topic.csdn.net/u/20080621/08/3397be02-47cd-40c3-9834-600cbb84c0bf.html
小写金额转换成大写
http://topic.csdn.net/u/20080118/11/80ff7847-20d9-45d8-9ed6-79933ced2cf8.html
整数转换成二进制的函数
http://topic.csdn.net/u/20071108/22/1650e0ef-3c14-4710-b2db-075f1e0badf3.html
SQLserver中实现一个函数，能够将10进制转化为36进制
http://topic.csdn.net/u/20070612/16/05e8b690-0818-479d-a34d-ee6258da4027.html
http://blog.csdn.net/roy_88/archive/2007/11/13/1882106.aspx
/*
功能:十进制/十八进制的互转换(此方法应用于所有进制与10进制的转换)
作者:roy_88
*/
http://topic.csdn.net/u/20080715/10/548c6e12-731f-4e54-ac13-d05325068032.html
/*
功能:整数转换成二进制的函数
作者:happyflystone
*/
SQL Server里面有十六进制转化为十进制的函数吗
http://topic.csdn.net/u/20070116/10/0c7d9ec1-acaa-4918-86cd-51516f7f517f.html
SQL2000加密解密函数
http://topic.csdn.net/t/20041202/20/3610469.html
想通过一个函数或sql语句，随机生成任意6个字母组成的字符
http://topic.csdn.net/u/20070627/17/ea906673-9821-46c6-b351-ab98a0b3d2c4.html
求一个sql函数：计算时间差（除周六和周日外）的天数
http://topic.csdn.net/u/20070322/12/2a8d5fcf-2d5b-49e1-b58a-c7277e15ee95.html
Ip算法匹配
http://topic.csdn.net/u/20080711/15/66639249-52d9-40c6-8f5c-131e49c1a6cf.html
http://topic.csdn.net/u/20080801/11/506af00d-b882-41a2-99d9-4dc6927101fd.html
http://blog.csdn.net/dobear_0922/archive/2007/12/18/1947219.aspx
/*
功能:SQL日历函数.
作者:dobear_0922
*/
http://topic.csdn.net/u/20080505/20/d2dffbbe-6d6b-41cf-b29a-41149540eafa.html
/*
功能:两个支持text字段内文字替换的存储过程
作者:realgz
*/
MS SQL Server 2000 中文模糊搜寻存储过程及函数
http://www.cppfans.com/d_vcl_files/mssqlxp_chn.asp
http://topic.csdn.net/t/20050419/10/3948336.html
/*
功能:全角/半角转换
*/
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html
/*
功能:合并与拆分
整理者:roy_88
*/
http://topic.csdn.net/u/20080605/11/55c273aa-2206-4ded-b6f6-508a7a755c6a.html
/*
功能:自定义函数，用户可以调用这个函数判断指定的字符串是否符合正则表达式的规则.
作者:ranzj
*/
T-SQL: 15 个与日期时间相关的精典语句函数T-SQL: 15 个与日期时间相关的自定义函数(UDF),周日作为周的最后一天,均不受 @@DateFirst、语言版本影响都是从老文章里收集或提炼出来的!提示:(@@Datefirst + datepart(weekday,@Date)) % 7 判断周几是最保险的! 与 @@DateFirst 无关,与语言版本无关@@DateFirst 可能会导致 datepart(weekday,@Date) 不一样!无论 @@DateFirst 等于几,无论是什么语言版本的 SQL Server 下面永远恒成立!(@@Datefirst + datepart(weekday,@Date)): 2、3、4、5、6、0、1 分别代表 周一 到 周日-- */create function udf_GetAge(@StartDate datetime,@EndDate datetime)returns integer-- 返回精确年龄 select dbo.udf_GetAge('1949-10-01',getdate())beginreturn datediff(year,@StartDate,@EndDate) - case when datediff(day,dateadd(year,datediff(year,@StartDate,@EndDate),@StartDate),@EndDate) >= 0 then 0 else 1 endendgocreate function udf_DaysOfYearByDate(@Date datetime)RETURNS integer-- 返回年的天数 可判断 平(365)、润(366) 年beginreturn datediff(day,dateadd(year,datediff(year,0,@Date),0),dateadd(year,datediff(year,0,@Date )+1,0))endgocreate function udf_DaysOfYear(@Year integer)RETURNS integer-- 返回年的天数 可判断 平(365)、润(366) 年beginreturn datediff(day,dateadd(year,@year - year(0),0),dateadd(year,@year - year(0) + 1,0))endgocreate function udf_HalfDay(@Date datetime)returns datetime-- 返回 @Date 是 上午 返回 @Date 的零点,@Date 是 下午 返回 @Date 的十二点asbeginreturn case when datepart(hour,@Date) gocreate function udf_WeekDiff(@StartDate datetime,@EndDate datetime)returns integer-- 返回 [@StartDate , @EndDate] 之间周数 周日是当周的最后一天beginreturn datediff(week,@StartDate,@EndDate) -- + 1 + case when (@@Datefirst + datepart(weekday,@StartDate)) % 7 = 1 then 1 else 0 end - case when (@@Datefirst + datepart(weekday,@EndDate)) % 7 = 1 then 1 else 0 endendgocreate function udf_WeekOfMonth(@Date datetime)-- 返回 @Date 是所在月的第几周 周日是当周的最后一天returns integerasbeginreturn datediff(week ,case when (@@Datefirst + datepart(weekday,dateadd(month,datediff(month,0,@Date),0))) % 7 = 1 then dateadd(month,datediff(month,0,@Date),0) - 1 else dateadd(month,datediff(month,0,@Date),0) end ,case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1 then @Date-1 else @Date end ) + 1endgocreate function udf_WeekOfQuarter(@Date datetime)-- 返回 @Date 是所在季度的第几周 周日是当周的最后一天returns intasbeginreturn datediff(week ,case when (@@Datefirst + datepart(weekday,dateadd(Quarter,datediff(Quarter,0,@Date),0))) % 7 = 1 then dateadd(Quarter,datediff(Quarter,0,@Date),0) - 1 else dateadd(Quarter,datediff(Quarter,0,@Date),0) end ,case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1 then @Date - 1 else @Date end ) + 1endgocreate function udf_WeekOfYear(@Date datetime)-- 返回 @Date 是所在年的第几周 周日是当周的最后一天returns intasbeginreturn datediff(week ,case when (@@Datefirst + datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@Date),0))))) % 7 = 1 then dateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@Date),0)))) else dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@Date),0))) --date 所在年的第一天 即: 一月一号 end ,case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1 then dateadd(day,-1,@Date) else @Date end ) + 1endgocreate function udf_WeekDay(@ int,@Date datetime)returns datetime-- 返回 @Date 映射到 所在周的其他天 周日是当周的最后一天begin/*--周日算作(上一)周的最后一天 当 @ = 7 代表将 @Date 映射到 所在周的星期日可用于按周汇总 Group by,均支持跨年跨月数据*/return dateadd(day ,case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 0 --周六 then case when @ between 1 and 6 then @ - 6 else 1 end when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1 --周日(七) then case when @ between 1 and 6 then @ - 7 else 0 end when (@@Datefirst + datepart(weekday,@Date)) % 7 between 2 and 6 --周一至周五 then case when @ between 1 and 6 then @ + 1 - (@@Datefirst + datepart(weekday,@Date)) % 7 else 8 - (@@Datefirst + datepart(weekday,@Date)) % 7 end end ,@Date)endgocreate function udf_WeekdayDiff(@Weekday integer,@StartDate datetime,@EndDate datetime)returns integer-- -- 返回 [@StartDate , @EndDate] 之间周一 到 周日的个数 周日是当周的最后一天begin-- @Weekday: 1: Monday , ... ,7: Sundayreturn datediff(week,@StartDate,@EndDate) + case when (@@Datefirst + datepart(weekday,@StartDate)) % 7 + case when (@@Datefirst + datepart(weekday,@StartDate)) % 7 = 0 then 7 else 0 end > @Weekday % 7 + 1 then 0 else 1 end - case when (@@Datefirst + datepart(weekday,@EndDate)) % 7 + case when (@@Datefirst + datepart(weekday,@EndDate)) % 7 = 0 then 7 else 0 end >= @Weekday % 7 + 1 then 0 else 1 end/* test:declare @b datetimedeclare @e datetimeset @b = '2004-01-29'set @e = '2004-09-05'select @b as BeginDate ,@e as EndDate,dbo.udf_WeekdayDiff(1,@b,@e) as CountOfMonday,dbo.udf_WeekdayDiff(2,@b,@e) as CountOfTuesday,dbo.udf_WeekdayDiff(3,@b,@e) as CountOfWednesday,dbo.udf_WeekdayDiff(4,@b,@e) as CountOfThursday,dbo.udf_WeekdayDiff(5,@b,@e) as CountOfFriday,dbo.udf_WeekdayDiff(6,@b,@e) as CountOfSaturday,dbo.udf_WeekdayDiff(7,@b,@e) as CountOfSunday*/endgocreate function udf_WeekdayID(@Date datetime)returns integer-- 返回 @Date 是 Monday 返回 1, ... ,是 Sunday 返回 1begin--1: Monday , ... ,7: Sundayreturn (@@Datefirst + datepart(weekday,@Date)) % 7 + case when (@@Datefirst + datepart(weekday,@Date)) % 7 gocreate function udf_NextWorkDate(@Date datetime)returns datetime-- 返回 @Date 的下一个工作日begin/*declare @i intset @i = 3declare @Date datetimeset @Date = '2005-01-02'-- */return case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 6 -- Friday then dateadd(day,3,@Date) when (@@Datefirst + datepart(weekday,@Date)) % 7 = 0 -- saturday then dateadd(day,2,@Date) else dateadd(day,1,@Date) endendgo
SQL code create function udf_PreviousWorkDate(@Date datetime)returns datetime-- 返回 @Date 的上一个工作日begin/*declare @i intset @i = 3declare @Date datetimeset @Date = '2005-01-02'-- */return case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 2 -- Monday then dateadd(day,-3,@Date) when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1 -- Sunday then dateadd(day,-2,@Date) else dateadd(day,-1,@Date) endendgocreate function udf_WorkDateAdd(@i integer,@Date datetime)returns datetime-- 返回 @Date 加上一段 @i 个工作日的新值begindeclare @ intset @ = 0while @ = 0 then --dbo.udf_nextworkdate(@Date) case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 6 -- Friday then dateadd(day,3,@Date) when (@@Datefirst + datepart(weekday,@Date)) % 7 = 0 -- saturday then dateadd(day,2,@Date) else dateadd(day,1,@Date) end else --dbo.udf_previousworkdate(@Date) case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 2 -- Monday then dateadd(day,-3,@Date) when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1 -- Sunday then dateadd(day,-2,@Date) else dateadd(day,-1,@Date) end end set @ = @ + 1 endreturn @Dateendgocreate function udf_GetStar (@ datetime)RETURNS varchar(100)-- 返回日期所属星座BEGINRETURN(--declare @ datetime--set @ = getdate()select max(star)from(select '魔羯座' as star,1 as [month],1 as [day]union all select '水瓶座',1,20union all select '双鱼座',2,19union all select '牡羊座',3,21union all select '金牛座',4,20union all select '双子座',5,21union all select '巨蟹座',6,22union all select '狮子座',7,23union all select '处女座',8,23union all select '天秤座',9,23union all select '天蝎座',10,24union all select '射手座',11,22union all select '魔羯座',12,22) starswhere dateadd(month,[month] - 1,dateadd(year,year(@) - year(0),0)) + [day] - 1 =(select max(dateadd(month,[month] - 1,dateadd(year,year(@) - year(0),0)) + [day] - 1)from (select '魔羯座' as star,1 as [month],1 as [day]union all select '水瓶座',1,20union all select '双鱼座',2,19union all select '牡羊座',3,21union all select '金牛座',4,20union all select '双子座',5,21union all select '巨蟹座',6,22union all select '狮子座',7,23union all select '处女座',8,23union all select '天秤座',9,23union all select '天蝎座',10,24union all select '射手座',11,22union all select '魔羯座',12,22) starswhere @ >= dateadd(month,[month] - 1,dateadd(year,year(@) - year(0),0)) + [day] - 1))endSQL Server 日期算法一周的第一天select @@DATEFIRST一个月的第一天 select dateadd(mm,datediff(mm,0,getdate()),0)本周的星期一 select dateadd(wk,datediff(wk,0,getdate()),0)一年的第一天 SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) 季度的第一天 SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) 当天的零时SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) 上个月的最后一天 ：本月第一天减2ms.SELECT dateadd(ms,-2,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) 本月的最后一天 SELECT dateadd(ms,-2,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) 本月的第一个星期一 去掉时分秒 DATEADD(day, DATEDIFF(day,0,getdate()), 0) 显示星期几 select datename(weekday,getdate()) 如何取得某个月的天数 SELECT Day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))) 判断是否闰年： SELECT case day(dateadd(mm, 2, dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)))) when 28 then '平年' else '闰年' end 一个季度多少天 declare @m tinyint,@time smalldatetime select @m=month(getdate()) select @m=case when @m between 1 and 3 then 1 when @m between 4 and 6 then 4 when @m between 7 and 9 then 7 else 10 end select @time=datename(year,getdate())+'-'+convert(varchar(10),@m)+'-01' select datediff(day,@time,dateadd(mm,3,@time))
/*=============================================*//* Author: roy_88 */--2 实现全角与半角字符转换的处理函数CREATE FUNCTION f_Convert(@str NVARCHAR(4000), --要转换的字符串@flag bit --转换标志,0转换成半角,1转换成全角)RETURNS nvarchar(4000)ASBEGIN DECLARE @pat nvarchar(8),@step int,@i int,@spc int IF @flag=0 SELECT @pat=N'%[！-～]%',@step=-65248, @str=REPLACE(@str,N' ',N' ') ELSE SELECT @pat=N'%[!-~]%',@step=65248, @str=REPLACE(@str,N' ',N' ') SET @i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str) WHILE @i>0 SELECT @str=REPLACE(@str, SUBSTRING(@str,@i,1), NCHAR(UNICODE(SUBSTRING(@str,@i,1))+@step)) ,@i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str) RETURN(@str)ENDGOdeclare T_cursor cursor local forselect a.Name, b.Name from sysobjects a join syscolumns b on a.ID=b.IDjoin systypes c on c.xusertype=b.Xtypewhere a.xtype='U' and c.Name in('nvarchar','nchar','varchar','char')declare @tabName sysname,@ColName sysnameopen T_cursorfetch next from T_cursor into @tabName,@ColNamewhile @@fetch_status=0begin exec('update '+@tabName+' set '+@ColName+'=dbo.f_Convert('+@ColName+',0) where PATINDEX(N''%[！-～]%'' COLLATE LATIN1_GENERAL_BIN'+','+@ColName+')>0' fetch next from T_cursor into @tabName,@ColNameendclose T_cursordeallocate T_cursor-----------------------------------------改列的数据全角为半角declare T_cursor cursor local forselect a.Name, b.Name from sysobjects a join syscolumns b on a.ID=b.IDjoin systypes c on c.xusertype=b.Xtypewhere a.xtype='U' and c.Name in('nvarchar','nchar','varchar','char')declare @tabName sysname,@ColName sysnameopen T_cursorfetch next from T_cursor into @tabName,@ColNamewhile @@fetch_status=0begin exec('update '+@tabName+' set '+@ColName+'=dbo.f_Convert('+@ColName+',0) where PATINDEX(N''%[！-～]%'' COLLATE LATIN1_GENERAL_BIN'+','+@ColName+')>0')--少了) fetch next from T_cursor into @tabName,@ColNameendclose T_cursordeallocate T_cursorgo--改列名全角为半角declare T_cursor cursor local forselect a.Name, b.Name from sysobjects a join syscolumns b on a.ID=b.IDwhere a.xtype='U' and PATINDEX(N'%[！-～]%' COLLATE LATIN1_GENERAL_BIN,b.Name)>0declare @tabName sysname,@ColName sysnameopen T_cursorfetch next from T_cursor into @tabName,@ColNamewhile @@fetch_status=0begin exec('exe sp_rename '''+@tabName+'.'+@ColName+''',''dbo.f_Convert('+@ColName+',0)''') fetch next from T_cursor into @tabName,@ColNameendclose T_cursordeallocate T_cursor--------------------------------------------------------改列名全角为半角declare T_cursor cursor local forselect a.Name+'.'+b.Name,NameNew=dbo.f_Convert(b.Name)from sysobjects a join syscolumns b on a.ID=b.IDwhere a.xtype='U' and PATINDEX(N'%[！-～]%' COLLATE LATIN1_GENERAL_BIN,b.Name)>0declare @tabName sysname,@ColName sysnameopen T_cursorfetch next from T_cursor into @tabName,@ColNamewhile @@fetch_status=0begin exe sp_rename @tabName,@ColName fetch next from T_cursor into @tabName,@ColNameendclose T_cursordeallocate T_cursor
SQL code
- 常用的sql函数
- SQL常用的函数
- SQL常用的函数
- 常用的sql函数
- 常用的sql函数
- 常用的sql函数
- sql常用的函数
- 常用的Sql 函数
- 一些SQL的常用函数
- 常用sql函数的用法
- 常用sql函数的用法
- Sql常用的日期函数
- 常用sql函数的用法
- SQL一些常用的函数
- SQL常用的字符串函数
- sql 常用的 基础函数
- SQL语言的常用函数
- SQL 几个常用的函数
- 公司5周年庆典，阳朔游玩
- Using SVN with Ant
- mo+vb实现沿国界运动
- Wince边做边学8
- 学Linux的原因
- SQL常用的函数
- Wince边做边学5
- IIS - Server Application Unavailable
- 入职培训—中国软件评测中心简介
- Java资源
- socket编程原理
- 怎样提高身体柔韧性
- JDK1.5+tomcat环境变量配置（适用初学者）
- 12个增加博客外部链接的方法
