SQL 笔记
1、根据数字转换成大写如：123=》一二三
/* Desc:将数字转换为大写 123=》一二三 123=》一百二十三(需要拓展) Email:gametheworld@126.com Author:叶康 UpdateDate:2015-12-10 13:34:06.160 QQ:865252662 select getdate() */ CREATE FUNCTION [dbo].[Fn_NumberConvertChinase](@number NVARCHAR(50)) RETURNS NVARCHAR(20) AS BEGIN DECLARE @res NVARCHAR(20) DECLARE @char CHAR(1) SET @res = '' SET @char = substring(@number,1,1) SELECT @res = (CASE @char WHEN '1' THEN '一' WHEN '2' THEN '二' WHEN '3' THEN '三' WHEN '4' THEN '四' WHEN '5' THEN '五' WHEN '6' THEN '六' WHEN '7' THEN '七' WHEN '8' THEN '八' WHEN '9' THEN '九' WHEN '0' THEN '零' ELSE substring(@number,1,1) END ) IF(len(@number) > 1) BEGIN SET @res = @res + dbo.fn_numberconvertchinase(substring(@number,2,len(@number)-1)) END RETURN @res END
2、获取文字的首个字母
/* Desc:获取文字的首个字母 会调用上面根据数字转换大写Function Email:gametheworld@126.com Author:叶康 UpdateDate:2015-12-10 17:30:33.813 QQ:865252662 select getdate() */ CREATE function fun_getPY(@str nvarchar(4000)) returns nvarchar(4000) as begin DECLARE @strlen INT; DECLARE @re NVARCHAR(4000); --字符中数字转大写 SELECT @str=dbo.fn_numberconvertchinase(@str); DECLARE @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1)) INSERT INTO @t(chr,letter) SELECT '吖','A' UNION ALL SELECT '八','B' UNION ALL SELECT '嚓','C' UNION ALL SELECT '咑','D' UNION ALL SELECT '妸','E' UNION ALL SELECT '发','F' UNION ALL SELECT '旮','G' UNION ALL SELECT '铪','H' UNION ALL SELECT '丌','J' UNION ALL SELECT '咔','K' UNION ALL SELECT '垃','L' UNION ALL SELECT '嘸','M' UNION ALL SELECT '拏','N' UNION ALL SELECT '噢','O' UNION ALL SELECT '妑','P' UNION ALL SELECT '七','Q' UNION ALL SELECT '呥','R' UNION ALL SELECT '仨','S' UNION ALL SELECT '他','T' UNION ALL SELECT '屲','W' UNION ALL SELECT '夕','X' UNION ALL SELECT '丫','Y' UNION ALL SELECT '帀','Z'; SELECT @strlen=len(@str),@re=''; WHILE @strlen>0 BEGIN SELECT TOP 1 @re=letter+@re,@strlen=@strlen-1 FROM @t a WHERE chr<=substring(@str,@strlen,1) ORDER BY chr DESC IF @@rowcount=0 SELECT @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1 END RETURN(UPPER(@re)) END
3、MD5加密
-- ============================================= -- Description: 实现MD5加密算法，返回对字符串的加密结果串 -- ============================================= CREATE FUNCTION MD5 ( -- 源字符串 @src nvarchar(4000), -- 加密类型(16/32) @type int = 32 ) RETURNS varchar(255) WITH EXECUTE AS CALLER AS BEGIN -- 存放md5加密串(ox) DECLARE @smd5 nvarchar(4000) -- 加密字符串此处用MD5加密，还有一种是SHA1加密 SELECT @smd5 = sys.fn_VarBinToHexStr(hashbytes('MD5', @src)); IF @type=16 SELECT @smd5 = SUBSTRING(@smd5,11,16) --16位 ELSE SELECT @smd5 = SUBSTRING(@smd5,3,32) --32位 -- 返回加密串 RETURN @smd5 END
4、随机一个区间中的数字
/* Desc:随机一个区间数字 Email:gametheworld@126.com Author:叶康 UpdateDate:2015-12-07 10:31:02.993 QQ:865252662 select getdate() */ CREATE VIEW ViewRand as SELECT re=rand() create function Get_RandNum( @SNum int, @ENum int) returns decimal(38,0) as begin declare @r decimal(38,0) select @r=cast(re*(@ENum-@SNum)+@SNum as decimal(38,0)) FROM ViewRand; return(@r) end go
5、随机一个区间时间
例如随一个一个时间 2015-12-01 至 2015-12-10日的时间，且：小时随机：范围在9点 至22点间
SELECT DBO.Get_RandTime('2015-12-01','2015-12-10',9,22)
/* Desc:随机时间 Email:gametheworld@126.com Author:叶康 UpdateDate:2015-12-10 13:34:06.160 QQ:865252662 select getdate() */ ALTER function [dbo].[Get_RandTime]( @Sdt datetime ,@Edt datetime ,@SRH INT=0 --小时区间起始 ,@ERH INT=24 --小时区间结束 ) returns datetime as begin declare @dt datetime DECLARE @Hour INT --小时 DECLARE @Minutes INT --分钟 DECLARE @Second INT --秒 DECLARE @MS INT --毫秒 SELECT @Hour=DBO.Get_RandNum(@SRH,@ERH); SELECT @Minutes=DBO.Get_RandNum(0,59); SELECT @Second=DBO.Get_RandNum(0,59); SELECT @MS=DBO.Get_RandNum(0,1000); DECLARE @TempTime nvarchar(50); set @TempTime=convert(char(10),@Edt,120)+' '+Convert(nvarchar(20),@Hour)+':'+Convert(nvarchar(20),@Minutes)+':'+Convert(nvarchar(20),@Second)+'.'+Convert(nvarchar(20),@MS); select @dt=dateadd(dd,datediff(dd,@Edt,convert(char(10),@Sdt,120))*re,Convert(datetime,@TempTime)) FROM ViewRand; return(@dt) end
6、以关键字分割成表
ALTER FUNCTION [dbo].[SplitToTable] ( @SplitString nvarchar(max), @Separator nvarchar(10)=' ' ) RETURNS @SplitStringsTable TABLE ( [id] int identity(1,1), [value] nvarchar(max) ) AS BEGIN DECLARE @CurrentIndex int; DECLARE @NextIndex int; DECLARE @ReturnText nvarchar(max); SELECT @CurrentIndex=1; WHILE(@CurrentIndex<=len(@SplitString)) BEGIN SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex); IF(@NextIndex=0 OR @NextIndex IS NULL) SELECT @NextIndex=len(@SplitString)+1; SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex); IF(LEN(@ReturnText)>0) BEGIN INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText); END SELECT @CurrentIndex=@NextIndex+1; END RETURN; END
7、随机手机号（数量在大的情况不容乐观，我随机了10w个用了两个小时，可以优化，别干坏事）
用法 exec yekang_water_RandomNumber 8,100000
/* Desc:随机手机号 Email:gametheworld@126.com Author:叶康 UpdateDate:2015-12-01 16:13:25.530 QQ:865252662 select getdate() */ ALTER PROCEDURE yekang_water_RandomNumber @Len INT = 1, --随机数位数 @Rows INT = 1 --随机个数 AS BEGIN DECLARE @PT AS TABLE(id int,item VARCHAR(MAX)); DECLARE @TS NVARCHAR(500); DECLARE @RUNB INT; --移动号段 SET @TS='134,135,136,137,138,139,147,150,151,152,157,158,159,178,182,183,184,187,188,'; --联通号段 SET @TS=@TS+'130,131,132,155,156,185,186,145,176,'; --电信号段 SET @TS=@TS+'133,153,177,180,181,189'; INSERT INTO @PT(id,item) select ROW_NUMBER()over(order by item desc) as id,* from dbo.SplitStringToTable(@TS,','); ----------------------随机位数 --DECLARE @T AS TABLE([Random Number] VARCHAR(MAX)) DECLARE @L INT = 1, @R INT = 1 WHILE @R <= @Rows BEGIN DECLARE @RN varchar(MAX) = '' WHILE @L <= @Len --随机产生每个随数的位数 BEGIN SET @RN = @RN + CHAR(ROUND(RAND() * 9 + 48,0)) SET @L = @L + 1 END select @RUNB=ROUND(RAND()*34,0); DECLARE @item nvarchar(5) --------前缀----------- select @item=item from @PT where id=@RUNB; ------------------- set @RN=@item+@RN; --如果产生相同的随机数，将不会存储 IF NOT EXISTS(SELECT Tel FROM a_water_Tels WHERE Tel = @RN) BEGIN INSERT INTO a_water_Tels(Tel)VALUES(@RN) --INSERT INTO @T SELECT @RN --INSERT INTO @T ([Random Number])VALUES (@RN) SET @R = @R + 1 --记录总共产生了几笔随机数 SET @L = 1 -- 每产生完一个随机数之后，将随机数的位数初始化为1 END END END
8、
