34、字符串转成16进制函数
/****************************
字符串转成16进制
作者：不得闲
QQ: 75492895
Email: appleak46@yahoo.com.cn
****************************/
--创建函数(suiyunonghen(不得闲))
Create Function VarCharToHex(@Str Varchar(400))
returns varchar(800)
as
begin
declare @i int,@Asi int,@ModS int,@res varchar(800),@Len int,@Cres varchar(4),@tempstr varbinary(400)
select @i = 1,@res='',@len=datalength(@str),@tempStr = Convert(varbinary,@str)
while @i<=@len
begin
Select @Asi = substring(@tempstr,1,1),@Cres=''
while @Asi <> 0
begin
select @Mods = @Asi %16,
@Cres=Case when (@Mods > 9) then Char(Ascii('A')+@Mods-10)+@Cres else Cast(@Mods as varchar(4)) + @Cres end,
@Asi = @Asi/16
end
Select @res = @res + @Cres,@tempStr = substring(@tempStr,2,@len-1),@i = @i+1
end
return @res
end
go
--测试示例
select dbo.VarCharToHex('叶子')
--运行结果
/*
D2B6D7D3
*/
35、去掉字段中多个带区号电话号码前面的区号
--原帖地址：http://blog.csdn.net/htl258/archive/2010/04/28/5540795.aspx
---------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-28 23:22:15
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
-- Subject: 应用实例：去掉字段中多个带区号电话号码前面的区号
--------------------------------------------------------------------
--需求贴：http://topic.csdn.net/u/20100428/20/f2572998-099c-463a-a530-707a40606c9c.html?53227
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([name] NVARCHAR(10),[phone] NVARCHAR(40))
INSERT [tb]
SELECT 'a',N'010-12345678/0571-86919111' UNION ALL
SELECT 'b',N'020-23950423/0756-34972654/023-89732456'
GO
--SELECT * FROM [tb]
-->SQL查询如下:
--1.创建自定义函数
IF NOT OBJECT_ID('[f_getphone]') IS NULL
DROP FUNCTION [f_getphone]
GO
CREATE FUNCTION f_getphone(@s varchar(200))
RETURNS varchar(200)
AS
BEGIN
SET @s=@s+'/'
DECLARE @r VARCHAR(200)
WHILE CHARINDEX('/',@s)>0
SELECT @r=ISNULL(@r+'/','')
+LEFT(STUFF(@s,1,CHARINDEX('-',@s),'')
,CHARINDEX('/',@s)-CHARINDEX('-',@s)-1)
,@s=STUFF(@s,1,CHARINDEX('/',@s),'')
RETURN @r
END
GO
--2.查询
SELECT [name],dbo.f_getphone(phone) 'phone' FROM TB
/*
name phone
---------- ------------------------------------
a 12345678/86919111
b 23950423/34972654/89732456
(2 行受影响)
*/
--本文来自CSDN博客
--转载请标明出处：--http://blog.csdn.net/htl258/archive/2010/04/28/5540795.aspx
36、SQL2000/2005字符串拆分为列表通用函数
-- 原帖地址：http://blog.csdn.net/htl258/archive/2010/04/28/5537235.aspx
------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-28 02:00:28
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:38
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
-- Subject: SQL2000/2005字符串拆分为列表通用函数
-------------------------------------------------------------------
--SQL2000/2005字符串拆分为列表通用函数
IF OBJECT_ID('f_getstr') IS NOT NULL
DROP FUNCTION f_getstr
GO
CREATE FUNCTION f_getstr(
@s NVARCHAR(4000), --待分拆的字符串
@flag NVARCHAR(10)='' --数据分隔符
)RETURNS @r TABLE(col NVARCHAR(1000))
AS
BEGIN
IF ISNULL(@flag,'')='' AND LEN(ISNULL(@flag,'')+'a')=1
INSERT @r
SELECT SUBSTRING(@s,number+1,1)
FROM master..spt_values
WHERE TYPE='p' and number<LEN(@s+'a')-1
ELSE
INSERT @r
SELECT SUBSTRING(@s,number,CHARINDEX(@flag,@s+@flag,number)-number)
FROM master..spt_values
WHERE TYPE='p' and number<=len(@s+'a')
--AND SUBSTRING(@flag+@s,number,1)=@flag --用此条件或下面的条件均可
AND CHARINDEX(@flag,@flag+@s,number)=number
RETURN
END
GO
--本实例技巧，利用master库自带的spt_values表，取number字段作为连续的序号，
--省去创建序号表，尽量做到通用，再加上字符串处理函数取得最终结果。
--1.每个字符拆分取出
SELECT * FROM dbo.f_getstr(N'一个世界一个家',NULL)
SELECT * FROM dbo.f_getstr(N'一个世界一个家','')
SELECT * FROM dbo.f_getstr(N'一个世界一个家',default)
/*
col
-------
一
个
世
界
一
个
家
(7 行受影响)
*/
--2.指定分隔符拆分取出
SELECT * FROM dbo.f_getstr(N'一个世界一个家',N' ')
SELECT * FROM dbo.f_getstr(N'一个,世界,一个,家',N',')
SELECT * FROM dbo.f_getstr(N'一个%世界%一个%家',N'%')
SELECT * FROM dbo.f_getstr(N'一个中国世界中国一个中国家',N'中国')
/*
col
---------
一个
世界
一个
家
(4 行受影响)
*/
--3.SQL2005以上版本可以结合apply进行拆分列值
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb (id INT,col VARCHAR(30))
INSERT INTO tb VALUES(1,'aa,bb')
INSERT INTO tb VALUES(2,'aaa,bbb,ccc')
GO
SELECT id,b.col FROM tb CROSS APPLY f_getstr(col,',') b
SELECT id,b.col FROM tb OUTER APPLY f_getstr(col,',') b
/*
id col
----------- -----------
aa
bb
aaa
bbb
ccc
(5 行受影响)
*/
--本文来自CSDN博客
--转载请标明出处：
--http://blog.csdn.net/htl258/archive/2010/04/28/5537235.aspx
37、求字符串中汉字的个数
一、分解字符串法
首先创建这个函数：
/*将字符串分解*/
create function [dbo].[SplitChar]
(
@str_One Nvarchar(100)
)
returns @Result table (col nvarchar(1))
AS
BEGIN
declare @number_One int
select @number_One=1
while @number_One<=len(@str_One)
begin
insert @Result select substring(@str_One,@number_One,1)
select @number_One=@number_One+1
end
return
END
然后创建如下函数
/*求字符串中汉字个数*/
Create function [dbo].[ChineseCountOne]
(
@Str_One nvarchar(200)
)
RETURNS int AS
BEGIN
declare @number_One int
SELECT @number_One =count(*) from dbo.SplitChar(@Str_One) where ascii(col)>127
return @number_One
END
/*使用示例
select dbo.[ChineseCountOne] ('China中国Beijing北京Olympics奥林匹克')
*/
二、字符字节求差法
首先创建这个函数：
create function [dbo].[Chinesecount_Two]
(
@Str_One varchar(200)
)
RETURNS int AS
BEGIN
declare @number_One int
set @number_One=(datalength(@Str_One)-len(@Str_One))
return @number_One
END
/*使用示例
DECLARE @sql_one varchar(200)
SET @sql_one='China中国Beijing北京Olympics奥林匹克'
DECLARE @sql_two nvarchar(200)
SET @sql_two='China中国Beijing北京Olympics奥林匹克'
select dbo.[Chinesecount_Two] (@sql_one) '个数one' ,
dbo.[Chinesecount_Two] (@sql_two) '个数two'
--此例说明此方法不受数据类型限制
*/38、得到条形码的校验位函数
二、SQL实现：
go
-- =============================================
-- Author: <Maco Wang>
-- Create date: <2009-10-27 16:01>
-- Description: <计算条形码的校验码>
-- =============================================
create function [dbo].[Get_CheckCode]
(
@ActiveCode varchar(12)
)
returns varchar(13)
as
begin
declare @codelength int
set @codelength= len(@ActiveCode)
declare @curnum int;set @curnum=0
declare @temp1 int;set @temp1=0
declare @temp2 int;set @temp2=0
declare @locatnum int;set @locatnum=0
declare @code13 int
declare @i int;set @i=0
while(@i<@codelength)
begin
set @locatnum=@i+2;
set @curnum=floor(substring(@ActiveCode,@codelength-@i,1))
if(@locatnum%2=0)
set @temp1=@temp1+@curnum
else
set @temp2=@temp2+@curnum
set @i=@i+1
end
set @code13=10-(@temp1*3+@temp2)%10;
if(@code13=10)
set @code13=0
return @ActiveCode+cast(@code13 as varchar(1));
end
--测试示例
select dbo.[Get_CheckCode]('827123121212')
--运行结果
/*
8271231212124
*/
39、根据年月得到当月的天数
go
--创建函数
create function [dbo].[get_days]
(
@yearmonth varchar(6) --参数格式：
)
returns int
as
begin
return (datediff(d,@yearmonth+'01',dateadd(mm,1,@yearmonth+'01')))
end
--测试示例
select dbo.[get_days]('201103')
--运行结果
/*
*/40、将一个正整数分解为m个2的n次方的和
-- =============================================
-- Author: <maco_wang>
-- Create date: <2011-01-21>
-- Description: <将一个正整数分解为m个2的n次方的和>
-- =============================================
go
--创建函数
CREATE function GetSumSequence(@num INT)
returns nvarchar(200)
AS
BEGIN
DECLARE @numc INT
SET @numc = @num
DECLARE @numstr VARCHAR(50)
SET @numstr = ''
WHILE ( @num <> 0 )
BEGIN
SET @numstr = @numstr + CONVERT(CHAR(1), @num % 2)
SET @num = @num / 2
END
--SELECT REVERSE(@numstr)
DECLARE @i INT
SET @i = LEN(@numstr)
DECLARE @j VARCHAR(MAX)
SET @j = ''
WHILE ( @i > 0 )
BEGIN
IF ( SUBSTRING(REVERSE(@numstr), LEN(@numstr) - @i + 1, 1) = '1' )
SELECT @j = @j + '+2^' + CAST (@i-1 AS VARCHAR(10))
SET @i = @i - 1
END
return (CAST(@numc AS VARCHAR(100)) + '=' + STUFF(@j, 1, 1, ''))
END
go
--测试示例
select dbo.GetSumSequence(12)
select dbo.GetSumSequence(65)
select dbo.GetSumSequence(892)
select dbo.GetSumSequence(1919191)
--运行结果
/*
12=2^3+2^2
65=2^6+2^0
892=2^9+2^8+2^6+2^5+2^4+2^3+2^2
1919191=2^20+2^19+2^18+2^16+2^14+2^11+2^7+2^6+2^4+2^2+2^1+2^0
*/
41、SQL位移运算函数
-- =============================================
-- Author: <maco_wang>
-- Create date: <2011-03-22>
-- Description: <SQL位移运算函数>
-- =============================================
go
--创建函数
create function displacement(@n as bigint,@m as varchar(3))
returns int
as
begin
declare @maco varchar(50);set @maco=''
declare @i int
declare @x int
declare @s int
while (@n<>0)
begin
set @maco=@maco+convert(char(1),@n%2)
set @n=@n/2
end
set @maco=reverse(@maco)
set @maco=RIGHT('0000'+@maco,4)
set @s=LEN(@maco)
set @i=convert(int,RIGHT(@m,1))
set @x=1
if LEFT(@m,2)='<<'
begin
while(@x<=@i)
begin
select @maco=@maco+'0'
select @x=@x+1
end
select @maco=RIGHT(@maco,@s)
end
if LEFT(@m,2)='>>'
begin
while(@x<=@i)
begin
select @maco='0'+@maco
select @x=@x+1
end
select @maco=LEFT(@maco,@s)
end
set @i=LEN(@maco)
set @s=0
while(@i>0)
begin
if SUBSTRING(@maco,LEN(@maco)-@i+1,1)='1'
begin
select @s=@s+POWER(2,convert(float,@i-1))
end
select @i=@i-1
end
return @s
end
--测试示例
select dbo.displacement(1,'<<3')
select dbo.displacement(8,'>>1')
select dbo.displacement(12,'>>3')
--运行结果
/*
*/42、得到汉字笔画函数
--===============================================
--功能：汉字笔画函数
--说明：以单个汉字汉字为参数返回每一个汉字的笔画数
--作者: J9988 --*/
--===============================================
create function [dbo].[fun_getbh](@char nchar(2))
returns int
as
begin
return(
case when unicode(@char) between 19968 and 40869 then(
select top 1 id from(
select id=1,ch=N'亅' union all select 2,N'阝'
union all select 3,N'马' union all select 4,N'风'
union all select 5,N'龙' union all select 6,N'齐'
union all select 7,N'龟' union all select 8,N'齿'
union all select 9,N'鸩' union all select 10,N'龀'
union all select 11,N'龛' union all select 12,N'龂'
union all select 13,N'龆' union all select 14,N'龈'
union all select 15,N'龊' union all select 16,N'龍'
union all select 17,N'龠' union all select 18,N'龎'
union all select 19,N'龐' union all select 20,N'龑'
union all select 21,N'龡' union all select 22,N'龢'
union all select 23,N'龝' union all select 24,N'齹'
union all select 25,N'龣' union all select 26,N'龥'
union all select 27,N'齈' union all select 28,N'龞'
union all select 29,N'麷' union all select 30,N'鸞'
union all select 31,N'麣' union all select 32,N'龖'
union all select 33,N'龗' union all select 35,N'齾'
union all select 36,N'齉' union all select 39,N'靐'
union all select 64,N'龘'
)a where ch>=@char collate Chinese_PRC_Stroke_CS_AS_KS_WS
order by id ASC) else 0 end)
end
--测试示例
select dbo.fun_getbh('晓')
--运行结果
/*
*/43、SQL数字转英文函数
--晴天兄(qianjin036a)的发帖地址： --http://topic.csdn.net/u/20080614/12/d26adea8-ac05-4b06-8b8a-f46a4b564e3b.html -- 数字转英文 -- ============================================= -- Author: qianjin036a -- Create date:06/14/2008 02:27:17 -- Description:Arabic numerals to English -- ============================================= go --创建函数 CREATE FUNCTION Digit2English ( @arabia decimal(38,17) ) RETURNS varchar(1000) AS BEGIN declare @atoe table(a int,e varchar(10)) insert into @atoe select 0,'zero' union all select 1,'one' union all select 2,'two' union all select 3,'three' union all select 4,'four' union all select 5,'five' union all select 6,'six' union all select 7,'seven' union all select 8,'eight' union all select 9,'nine' declare @integer bigint,@trillion int,@billion int,@million int,@thousand int,@hundred int,@english varchar(1000) select @integer=@arabia,@english='' select @trillion=@integer % 1000000000000000/1000000000000,@billion=@integer % 1000000000000/1000000000, @million=@integer % 1000000000/1000000,@thousand=(@integer % 1000000)/1000,@hundred=(@integer % 1000) if @trillion>0 set @english=@english + dbo.ThreeDigit(@trillion) + 'trillion ' if @billion>0 set @english=@english + dbo.ThreeDigit(@billion) + 'billion ' if @million>0 set @english=@english + dbo.ThreeDigit(@million) + 'million ' if @thousand>0 set @english=@english + dbo.ThreeDigit(@thousand) + 'thousand ' if @hundred>0 set @english=@english + dbo.ThreeDigit(@hundred) if @english='' set @english='zero ' if @arabia-@integer>0.000000000 begin declare @decimal decimal(18,17) select @english=@english+'point ',@decimal=@arabia-@integer while @decimal>0.0 begin select @english=@english+e+' ' from @atoe where cast(@decimal*10 as int)=a set @decimal=@decimal*10-cast(@decimal*10 as int) end end return @english END GO -- ============================================= -- Author: qianjin036a -- Create date: 06/14/2008 02:27:17 -- Description: Three Digit Arabic numerals to English -- ============================================= CREATE FUNCTION ThreeDigit ( @integer int ) RETURNS varchar(100) WITH EXECUTE AS CALLER AS BEGIN declare @atoe table(a int,e varchar(10)) insert into @atoe select 0,'zero' union all select 1,'one' union all select 2,'two' union all select 3,'three' union all select 4,'four' union all select 5,'five' union all select 6,'six' union all select 7,'seven' union all select 8,'eight' union all select 9,'nine' union all select 10,'ten' union all select 11,'eleven' union all select 12,'twelve' union all select 13,'thirteen' union all select 14,'fourteen' union all select 15,'fifteen' union all select 16,'sixteen' union all select 17,'seventeen' union all select 18,'eighteen' union all select 19,'nineteen' union all select 20,'twenty' union all select 30,'thirty' union all select 40,'forty' union all select 50,'fifty' union all select 60,'sixty' union all select 70,'severty' union all select 80,'eighty' union all select 90,'ninety' declare @english varchar(100) set @english='' if @integer>99 begin select @english=e+' hundred ' from @atoe where @integer/100=a set @integer=@integer % 100 if @integer>0 set @english=@english+'and ' end if @integer<=20 and @integer>0 select @english=@english+e+' ' from @atoe where @integer=a if @integer>20 begin select @english=@english+e+' ' from @atoe where @integer/10*10=a set @integer=@integer % 10 if @integer>0 select @english=@english+e+' ' from @atoe where @integer=a end RETURN @english END GO select dbo.digit2english(123456789987654.321) union all select dbo.digit2english(120045080045054.8412) union all select dbo.digit2english(0.0102541) go /* --------------------------------------------------------------------- one hundred and twenty three trillion four hundred and fifty six billion seven hundred and eighty nine million nine hundred and eighty seven thousand six hundred and fifty four point three two one one hundred and twenty trillion forty five billion eighty million forty five thousand fifty four point eight four one two zero point zero one zero two five four one */
44、全角半角转换函数
--(此函数部分思路参考了CSDN上大力的转换函数)
--邹建2005.01(引用请保留此信息)--*/
go
--创建函数
create function SBC2DBC
(
@str nvarchar(4000), --要转换的字符串
@flag bit --转换标志,0转换成半角,1转换成全角
)
returns nvarchar(4000)
as
begin
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)
end
--测试示例
select dbo.SBC2DBC('~~~~ca!b',1)
--运行结果
/*
～～～～ｃａ！ｂ
*/
--附半角全角表
/*
ASCII 全角字符 Unicode 半角字符 Unicode
0x20 " "空格U+3000 " "空格U+0020
0x21 ！U+ff01 ! U+0021
0x22 ＂U+ff02 " U+0022
0x23 ＃U+ff03 # U+0023
0x24 ＄U+ff04 $ U+0024
0x25 ％U+ff05 % U+0025
0x26 ＆U+ff06 & U+0026
0x27 ＇U+ff07 ' U+0027
0x28 （U+ff08 ( U+0028
0x29 ）U+ff09 ) U+0029
0x2a ＊U+ff0a * U+002a
0x2b ＋U+ff0b + U+002b
0x2c ，U+ff0c , U+002c
0x2d －U+ff0d - U+002d
0x2e ．U+ff0e . U+002e
0x2f ／U+ff0f / U+002f
0x30 ０U+ff10 0 U+0030
0x31 １U+ff11 1 U+0031
0x32 ２U+ff12 2 U+0032
0x33 ３U+ff13 3 U+0033
0x34 ４U+ff14 4 U+0034
0x35 ５U+ff15 5 U+0035
0x36 ６U+ff16 6 U+0036
0x37 ７U+ff17 7 U+0037
0x38 ８U+ff18 8 U+0038
0x39 ９U+ff19 9 U+0039
0x3a ：U+ff1a : U+003a
0x3b ；U+ff1b ; U+003b
0x3c ＜U+ff1c < U+003c
0x3d ＝U+ff1d = U+003d
0x3e ＞U+ff1e > U+003e
0x3f ？U+ff1f ? U+003f
0x40 ＠U+ff20 @ U+0040
0x41 ＡU+ff21 A U+0041
0x42 ＢU+ff22 B U+0042
0x43 ＣU+ff23 C U+0043
0x44 ＤU+ff24 D U+0044
0x45 ＥU+ff25 E U+0045
0x46 ＦU+ff26 F U+0046
0x47 ＧU+ff27 G U+0047
0x48 ＨU+ff28 H U+0048
0x49 ＩU+ff29 I U+0049
0x4a ＪU+ff2a J U+004a
0x4b ＫU+ff2b K U+004b
0x4c ＬU+ff2c L U+004c
0x4d ＭU+ff2d M U+004d
0x4e ＮU+ff2e N U+004e
0x4f ＯU+ff2f O U+004f
0x50 ＰU+ff30 P U+0050
0x51 ＱU+ff31 Q U+0051
0x52 ＲU+ff32 R U+0052
0x53 ＳU+ff33 S U+0053
0x54 ＴU+ff34 T U+0054
0x55 ＵU+ff35 U U+0055
0x56 ＶU+ff36 V U+0056
0x57 ＷU+ff37 W U+0057
0x58 ＸU+ff38 X U+0058
0x59 ＹU+ff39 Y U+0059
0x5a ＺU+ff3a Z U+005a
0x5b ［U+ff3b [ U+005b
0x5c ＼U+ff3c / U+005c
0x5d ］U+ff3d ] U+005d
0x5e ＾U+ff3e ^ U+005e
0x5f ＿U+ff3f _ U+005f
0x60 ｀U+ff40 ` U+0060
0x61 ａU+ff41 a U+0061
0x62 ｂU+ff42 b U+0062
0x63 ｃU+ff43 c U+0063
0x64 ｄU+ff44 d U+0064
0x65 ｅU+ff45 e U+0065
0x66 ｆU+ff46 f U+0066
0x67 ｇU+ff47 g U+0067
0x68 ｈU+ff48 h U+0068
0x69 ｉU+ff49 i U+0069
0x6a ｊU+ff4a j U+006a
0x6b ｋU+ff4b k U+006b
0x6c ｌU+ff4c l U+006c
0x6d ｍU+ff4d m U+006d
0x6e ｎU+ff4e n U+006e
0x6f ｏU+ff4f o U+006f
0x70 ｐU+ff50 p U+0070
0x71 ｑU+ff51 q U+0071
0x72 ｒU+ff52 r U+0072
0x73 ｓU+ff53 s U+0073
0x74 ｔU+ff54 t U+0074
0x75 ｕU+ff55 u U+0075
0x76 ｖU+ff56 v U+0076
0x77 ｗU+ff57 w U+0077
0x78 ｘU+ff58 x U+0078
0x79 ｙU+ff59 y U+0079
0x7a ｚU+ff5a z U+007a
0x7b ｛U+ff5b { U+007b
0x7c ｜U+ff5c | U+007c
0x7d ｝U+ff5d } U+007d
0x7e ～U+ff5e ~ U+007e
*/
45、返回两个时间范围内的一个随机时间
/******************************
* 函数名：RandDateTime
* 作用： 返回两个时间范围内的一个随机时间
* Author: 兰习刚
* Date: 2009-11-30
*******************************/
go
--创建函数
create Function RandDateTime
(
@RandNum Decimal(38,18),--0-1之际随机数值建议Rand()
@StartTime DateTime, --第一个时间
@EndTime DateTime --第二个时间
)
Returns DateTime
As
Begin
Declare @HourDiff Decimal(38,18) --两个时间之间的小时差值
Declare @MsPartDiff Decimal(38,18) --毫秒部分的差值
Declare @SmallDate DateTime
Declare @ReturnDateTime DateTime
/*取各部分差值*/
Set @HourDiff = DateDiff(hh,@StartTime,@EndTime)
Set @MsPartDiff = Abs(DateDiff(ms,DateAdd(hh,@HourDiff,@StartTime),@EndTime))
Select @SmallDate=(Case When @HourDiff>0 Then @StartTime Else @EndTime End) --取较小的时间
Set @HourDiff = Abs(@HourDiff)
ActionLable:
Declare @HourDecimal Decimal(38,18) --小时的小数部分
Declare @HourString varchar(200)
Set @HourDiff = @HourDiff * @RandNum
Set @HourString = CONVERT(VARCHAR(200),@HourDiff)
Set @HourString = SubString(@HourString,CharIndex('.',@HourString)+1,Len(@HourString))
Set @HourString = '0.' + @HourString
Set @HourDecimal = Convert(Decimal(38,18),@HourString) --获得小时的小数部分
Set @MsPartDiff = (@MsPartDiff + @HourDecimal * 3600*1000) * @RandNum
/*毫秒差值
由于之前@MsPartDiff是两个时间小时之后的毫秒差值
@HourDecimal * 3600*1000 有小时的小数部分的毫秒差值不会大于小时
毫秒不会溢出
*/
Set @ReturnDateTime = DateAdd(hh,@HourDiff,@SmallDate)
Set @ReturnDateTime = DateAdd(ms,@MsPartDiff,@ReturnDateTime)
Return @ReturnDateTime
End
--测试示例
select dbo.RandDateTime(Rand(),'2011-03-21 00:00:00.000','2011-03-21 23:59:00.000')
go 10
--运行结果
/*
-----------------------
2011-03-21 16:44:58.990
(1 row(s) affected)
-----------------------
2011-03-21 00:00:33.313
(1 row(s) affected)
-----------------------
2011-03-21 15:04:58.777
(1 row(s) affected)
-----------------------
2011-03-21 06:32:21.347
(1 row(s) affected)
-----------------------
2011-03-21 15:11:51.047
(1 row(s) affected)
-----------------------
2011-03-21 14:39:23.597
(1 row(s) affected)
-----------------------
2011-03-21 07:24:17.247
(1 row(s) affected)
-----------------------
2011-03-21 06:15:49.653
(1 row(s) affected)
-----------------------
2011-03-21 02:06:14.757
(1 row(s) affected)
-----------------------
2011-03-21 10:49:18.370
(1 row(s) affected)
*/
46、获取元素个数的函数
go
-- 创建函数(作者：csdn邹建)
create function getstrarrlength (@str varchar(8000))
returns int
as
begin
declare @int_return int
declare @start int
declare @next int
declare @location int
select @str =','+ @str +','
select @str=replace(@str,',,',',')
select @start =1
select @next =1
select @location = charindex(',',@str,@start)
while (@location <>0)
begin
select @start = @location +1
select @location = charindex(',',@str,@start)
select @next =@next +1
end
select @int_return = @next-2
return @int_return
end
-- 测试示例
SELECT [dbo].[getstrarrlength]('1,2,3,4,a,b,c,d')
--运行结果
/*
*/
/*
说明：
我开始考虑直接看逗号的个数，用replace替换逗号，求长度差就可以了，但是这里这个函数两个逗号相邻做了处理。
*/
47、获取指定索引的值的函数
go
--创建函数(作者：csdn邹建)
create function getstrofindex (@str varchar(8000),@index int =0)
returns varchar(8000)
as
begin
declare @str_return varchar(8000)
declare @start int
declare @next int
declare @location int
select @start =1
select @next =1 --如果习惯从开始则select @next =0
select @location = charindex(',',@str,@start)
while (@location <>0 and @index > @next )
begin
select @start = @location +1
select @location = charindex(',',@str,@start)
select @next =@next +1
end
if @location =0 select @location =len(@str)+1 --如果是因为没有逗号退出，则认为逗号在字符串后
select @str_return = substring(@str,@start,@location -@start) --@start肯定是逗号之后的位置或者就是初始值
if (@index <> @next ) select @str_return = '' --如果二者不相等，则是因为逗号太少，或者@index小于@next的初始值。
return @str_return
end
--测试示例
SELECT [dbo].[getstrofindex]('1,2,3,4,a,b,c,d',4)
--运行结果
/*
*/
--备注：类似功能的函数happyflystone （无枪狙击手）也写过一个，参数上做了扩展，可以定义分隔符了，在【叶子函数分享十六】我曾经发过。
48、根据年得到所有星期日的日期
go
--创建函数
create function GetWeekDays(@year int)
returns @t table (星期天varchar(20))
as
begin
insert @t
select substring(convert(varchar,dateadd(day,x,col),120),1,10) from
( select cast(cast(@year as varchar(4))+'-1-1' as datetime) as col )a cross join
( select top 365 b8.i+b7.i + b6.i + b5.i + b4.i +b3.i +b2.i + b1.i + b0.i x
from(select 0 i union all select 1) b0
cross join(select 0 i union all select 2) b1
cross join(select 0 i union all select 4) b2
cross join(select 0 i union all select 8) b3
cross join(select 0 i union all select 16) b4
cross join(select 0 i union all select 32) b5
cross join(select 0 i union all select 64) b6
cross join(select 0 i union all select 128) b7
cross join(select 0 i union all select 256) b8
order by 1 )b where datepart(dw,dateadd(day,x,col))=1
return
end
--测试示例
select * from dbo.GetWeekDays(2011)
--运行结果
/*
星期天
--------------------
2011-01-02
2011-01-09
2011-01-16
2011-01-23
2011-01-30
2011-02-06
2011-02-13
2011-02-20
2011-02-27
2011-03-06
2011-03-13
2011-03-20
2011-03-27
2011-04-03
2011-04-10
2011-04-17
2011-04-24
2011-05-01
2011-05-08
2011-05-15
2011-05-22
2011-05-29
2011-06-05
2011-06-12
2011-06-19
2011-06-26
2011-07-03
2011-07-10
2011-07-17
2011-07-24
2011-07-31
2011-08-07
2011-08-14
2011-08-21
2011-08-28
2011-09-04
2011-09-11
2011-09-18
2011-09-25
2011-10-02
2011-10-09
2011-10-16
2011-10-23
2011-10-30
2011-11-06
2011-11-13
2011-11-20
2011-11-27
2011-12-04
2011-12-11
2011-12-18
2011-12-25
(52 row(s) affected)
*/
49、生成两个时间之间的所有日期
--改写liangCK的部分代码为函数
--创建函数
create function generateTime
(
@begin_date datetime,
@end_date datetime
)
returns @t table(date datetime)
as
begin
with maco as
(
select @begin_date AS date
union all
select date+1 from maco
where date+1 <=@end_date
)
insert into @t
select * from maco option(maxrecursion 0);
return
end
go
--测试示例
select * from dbo.generateTime('2009-01-01','2009-01-10')
--运行结果
/*
date
-----------------------
2009-01-01 00:00:00.000
2009-01-02 00:00:00.000
2009-01-03 00:00:00.000
2009-01-04 00:00:00.000
2009-01-05 00:00:00.000
2009-01-06 00:00:00.000
2009-01-07 00:00:00.000
2009-01-08 00:00:00.000
2009-01-09 00:00:00.000
2009-01-10 00:00:00.000
*/
go
--第二版
--创建函数
create function generateTimeV2
(
@begin_date datetime,
@end_date datetime
)
returns @t table(date datetime)
as
begin
insert into @t
select dateadd(dd,number,@begin_date) AS date
from master..spt_values
where type='p' and dateadd(dd,number,@begin_date)<=@end_date
return
end
--测试示例
select * from dbo.generateTimeV2('2009-01-01','2009-01-10')
--运行结果
/*
date
-----------------------
2009-01-01 00:00:00.000
2009-01-02 00:00:00.000
2009-01-03 00:00:00.000
2009-01-04 00:00:00.000
2009-01-05 00:00:00.000
2009-01-06 00:00:00.000
2009-01-07 00:00:00.000
2009-01-08 00:00:00.000
2009-01-09 00:00:00.000
2009-01-10 00:00:00.000
(10 row(s) affected)
*/
50、无序字符比较函数
go
--创建函数(第一版)
create function get_orderstr(@str varchar(8000))
returns varchar(8000)
as
begin
set @str=rtrim(@str)
declare @tb table(s varchar(1),a int)
while len(@str)>0
begin
insert into @tb select left(@str,1),ascii(left(@str,1))
set @str=right(@str,len(@str)-1)
end
declare @sql varchar(8000)
select @sql=isnull(@sql+'','')+s from @tb order by a
return isnull(@sql,'')
end
--测试示例
if(dbo.get_orderstr('abc')=dbo.get_orderstr('acb'))
print '相同'
else
print '不同'
--运行结果
/*
相同
*/
--第二版
/*
* 功能：不按先后顺序比较字符串序列是否相同 *
* 适用：SQL Server 2000 / SQL Server 2005 *
* 返回：相同不相同 *
* 作者：Flystone *
* 描述：学习Limpire(昨夜小楼)的方法后做一个动态SQL的*
*/
go
--创建存储过程(这个不是函数)
CREATE proc sp_CompareString
@Str1 varchar(100),
@Str2 varchar(100),
@Split varchar(10),
@ret int output
AS
BEGIN
declare @Len int, @Sub varchar(100)
if @Str1 = @Str2 return(1)
if len(@Str1) <> len(@Str2) or len(replace(@Str1, @Split, '')) <> len(replace(@Str2, @Split, ''))
begin
set @ret = 0
return
end
set @str1 = 'select '''+replace(@str1,@Split,''' as col union all select ''')+''''
set @str2 = 'select '''+replace(@str2,@Split,''' as col union all select ''')+''''
declare @s nvarchar(4000)
set @s = '
if exists(select 1 from ('+@str1+') a where not exists(select 1 from ('+@str2+') b where a.col = b.col)
or
exists(select 1 from ('+@str2+') a where not exists(select 1 from ('+@str1+') b where a.col = b.col)
))
select @ret = 0
else
select @ret = 1'
exec sp_executesql @s,N'@ret int output',@ret output
END
GO
--测试示例
declare @ret int
exec sp_CompareString 'a,b,c', 'b,c,a', ',',@ret out
select @ret
exec sp_CompareString 'a,b,c', 'b,c,c,a', ',',@ret out
select @ret
drop proc sp_CompareString
go
--第三版
/* * * * * * * * * * * * * * * * * * * * * * *
* 功能：不按先后顺序比较字符串序列是否相同*
* 适用：SQL Server 2000 / SQL Server 2005 *
* 返回：相同不相同 *
* 作者：Limpire(昨夜小楼) *
* * * * * * * * * * * * * * * * * * * * * * */
--创建函数
CREATE FUNCTION fn_CompareString
(
@Str1 varchar(100),
@Str2 varchar(100),
@Split varchar(10)
)
RETURNS bit
AS
BEGIN
declare @Len int, @Sub varchar(100)
if @Str1 = @Str2 return(1)
if len(@Str1) <> len(@Str2) or len(replace(@Str1, @Split, '')) <> len(replace(@Str2, @Split, '')) return(0)
select @Len = len(@Split), @Str1 = @Split + @Str1 + @Split, @Str2 = @Split + @Str2 + @Split
while charindex(@Split, @Str1, @Len + 1) > 0
begin
set @Sub = left(@Str1, charindex(@Split, @Str1, @Len + 1) + @Len - 1)
if charindex(@Sub, @Str2) = 0 return(0)
while charindex(@Sub, @Str1) > 0 set @Str1 = replace(@Str1, @Sub, ',')
while charindex(@Sub, @Str2) > 0 set @Str2 = replace(@Str2, @Sub, ',')
if len(@Str1)<>len(@Str2) return(0)
end
return(1)
END
GO
--测试示例
select dbo.fn_CompareString('a,b,c', 'b,c,a', ',')
select dbo.fn_CompareString('a,b,c', 'b,c,c,a', ',')
--运行结果
/*
*/
51、在SQL SERVER中实现RSA加解密函数（第一版）
/***************************************************
作者：herowang(让你望见影子的墙）
日期：2010.1.1
注： 转载请保留此信息
更多内容，请访问我的博客：blog.csdn.net/herowang
****************************************************/
一、RSA算法原理
RSA算法非常简单，概述如下：
找两素数p和q
取n=p*q
取t=(p-1)*(q-1)
取任何一个数e,要求满足e<t并且e与t互素（就是最大公因数为）
取d*e%t==1
这样最终得到三个数：n d e
设消息为数M (M <n)
设c=(M**d)%n就得到了加密后的消息c
设m=(c**e)%n则m == M，从而完成对c的解密。
注：**表示次方,上面两式中的d和e可以互换。
在对称加密中：
n d两个数构成公钥，可以告诉别人；
n e两个数构成私钥，e自己保留，不让任何人知道。
给别人发送的信息使用e加密，只要别人能用d解开就证明信息是由你发送的，构成了签名机制。
别人给你发送信息时使用d加密，这样只有拥有e的你能够对其解密。
rsa的安全性在于对于一个大数n，没有有效的方法能够将其分解从而在已知n d的情况下无法获得e；同样在已知n e的情况下无法求得d。
以上内容出自原文出处http://www.xfocus.net/articles/200503/778.html
二、使用T-SQL实现RSA算法
--判断是否为素数
if object_id('f_pnumtest') is not null
drop function f_isPrimeNum
go
create function [dbo].[f_isPrimeNum]
(@p int)
returns bit
begin
declare @flg bit,@i int
select @flg=1, @i=2
while @i<=sqrt(@p)
begin
if(@p%@i=0 )
begin
set @flg=0
break
end
set @i=@i+1
end
return @flg
end
--判断两个数是否互素，首先要选取两个互素的数
if object_id('f_isNumsPrime') is not null
drop function f_isNumsPrime
go
create function f_isNumsPrime
(@num1 int,@num2 int)
returns bit
begin
declare @tmp int,@flg bit
set @flg=1
while (@num2%@num1<>0)
begin
select @tmp=@num1,@num1=@num2%@num1,@num2=@tmp
end
if @num1=1
set @flg=0
return @flg
end
--产生密钥对
if object_id('p_createKey1') is not null
drop proc p_createKey1
go
create proc p_createKey1
@p int,@q int
as
begin
declare @n bigint,@t bigint,@flag int,@d int
if dbo.f_pnumtest(@p)=0
begin
print cast(@p as varchar)+'不是素数，请重新选择数据'
return
end
if dbo.f_pnumtest(@q)=0
begin
print cast(@q as varchar)+'不是素数，请重新选择数据'
return
end
print '请从下列数据中选择其中一对，作为密钥'
select @n=@p*@q,@t=(@p-1)*(@q-1)
declare @e int
set @e=2
while @e<@t
begin
if dbo.f_isNUmsPrime(@e,@t)=0
begin
set @d=2
while @d<@n
begin
if(@e*@d%@t=1)
print cast(@e as varchar)+space(5)+cast(@d as varchar)
set @d=@d+1
end
end
set @e=@e+1
end
end
/*加密函数说明，@key 为上一个存储过程中选择的密码中的一个,@p ,@q 产生密钥对时选择的两个数。获取每一个字符的ascii值，然后进行加密，产生个字节的位数据*/
if object_id('f_RSAEncry') is not null
drop function f_RSAEncry
go
create function f_RSAEncry
(@s varchar(100),@key int ,@p int ,@q int)
returns varchar(8000)
as
begin
declare @crypt varchar(8000)
set @crypt=''
while len(@s)>0
begin
declare @i int,@tmp varchar(10),@k2 int,@leftchar int
select @leftchar=ascii(left(@s,1)),@k2=@key,@i=1
while @k2>0
begin
set @i=(@leftchar*@i)%(@p*@q)
set @k2=@k2-1
end
set @tmp=''
select @tmp=case when @i%16 between 10 and 15 then char( @i%16+55) else cast(@i%16 as varchar) end +@tmp,@i=@i/16
from (select number from master.dbo.spt_values where type='p' and number<10 )K
order by number desc
set @crypt=@crypt+right(@tmp,4)
set @s=stuff(@s,1,1,'')
end
return @crypt
end
--解密：@key 为一个存储过程中选择的密码对中另一个数字,@p ,@q 产生密钥对时选择的两个数
if object_id('f_RSADecry') is not null
drop function f_RSADecry
go
create function f_RSADecry
(@s varchar(100),@key int ,@p int ,@q int)
returns varchar(8000)
as
begin
declare @crypt varchar(8000)
set @crypt=''
while len(@s)>0
begin
declare @i int
select @i=sum(data1)
from ( select case upper(substring(left(@s,4), number, 1)) when 'A' then 10
when 'B' then 11
when 'C' then 12
when 'D' then 13
when 'E' then 14
when 'F' then 15
else substring(left(@s,4), number, 1)
end* power(16, len(left(@s,4)) - number) data1
from (select number from master.dbo.spt_values where type='p')K
where number <= len(left(@s,4))
) L
declare @k2 int,@j int
select @k2=@key,@j=1
while @k2>0
begin
set @j=(@i*@j)%(@p*@q)
set @k2=@k2-1
end
set @crypt=@crypt+char(@j)
set @s=stuff(@s,1,4,'')
end
return @crypt
end
三、在SQL SERVER中的使用
--【测试】
if object_id('tb') is not null
drop table tb
go
create table tb(id int identity(1,1),col varchar(100))
go
insert into tb values(dbo.f_RSAEncry('RSA',63,47,59))
select * from tb
--运行结果：
/*
id col
----------- ------------
069505EE02F3
*/
select id,col=dbo.f_RSADecry(col,847,47,59) from tb
--运行结果：
/*
id col
----------- -----------
RSA
*/
四、目前版本函数的缺点
1、目前只能对ascii符号进行加密，对unicode尚不支持。
2、在选取的素数都比较小，所以密钥空间比较小，而实际应用中选取的素数都会非常的大，不容易破解。但是对于一些基础的加密还能够使用。
3、如果一次加密觉得安全性不够的话，可以进行重复加密（即进行多次加密），两次的密钥最好不相同。
例如：insert into tb values(dbo.f_RSAEncry(dbo.f_RSAEncry('RSA',63,47,59),23,11,17))
那么解密的时候，按照加密的逆序进行解密：
select id,col=dbo.f_RSADecry(dbo.f_RSADecry(col,7,11,17),847,47,59)
from tb
4、如果选取的数字比较大，那么在进行加密的时候，生成的进制密文最好使用个字节或者更多。
52、在SQL SERVER中实现RSA加解密函数（第二版）
/***************************************************
作者：herowang(让你望见影子的墙）
日期：2010.1.5
注： 转载请保留此信息
更多内容，请访问我的博客：blog.csdn.net/herowang
****************************************************/
/*
本次修改增加了unicode的支持，但是加密后依然显示为进制数据，因为进行RSA加密后所得到的unicode编码是无法显示的，所以密文依然采用进制数据显示。
需要特别注意：如果要对中文进行加密，那么所选取的两个素数要比较大，两个素数的成绩最好要大于，即大于unicode的最大编码值
另外修改了第一个版本的部分函数名称
*/
在SQL SERVER中实现RSA加密算法
--判断是否为素数
if object_id('f_primeNumTest') is not null
drop function f_primeNumTest
go
create function [dbo].[f_primeNumTest]
(@p int)
returns bit
begin
declare @flg bit,@i int
select @flg=1, @i=2
while @i<=sqrt(@p)
begin
if(@p%@i=0 )
begin
set @flg=0
break
end
set @i=@i+1
end
return @flg
end
go
--测试示例：
select [dbo].[f_primeNumTest](23)--1
select [dbo].[f_primeNumTest](24)--0
select [dbo].[f_primeNumTest](25)--0
select [dbo].[f_primeNumTest](26)--0
select [dbo].[f_primeNumTest](27)--0
--判断两个数是否互素
if object_id('f_isNumsPrime') is not null
drop function f_isNumsPrime
go
create function f_isNumsPrime
(@num1 int,@num2 int)
returns bit
begin
declare @tmp int,@flg bit
set @flg=1
while (@num2%@num1<>0)
begin
select @tmp=@num1,@num1=@num2%@num1,@num2=@tmp
end
if @num1=1
set @flg=0
return @flg
end
go
--产生密钥对
if object_id('p_createKey') is not null
drop proc p_createKey
go
create proc p_createKey
@p int,@q int
as
begin
declare @n bigint,@t bigint,@flag int,@d int
if dbo.f_primeNumTest(@p)=0
begin
print cast(@p as varchar)+'不是素数，请重新选择数据'
return
end
if dbo.f_primeNumTest(@q)=0
begin
print cast(@q as varchar)+'不是素数，请重新选择数据'
return
end
print '请从下列数据中选择其中一对，作为密钥'
select @n=@p*@q,@t=(@p-1)*(@q-1)
declare @e int
set @e=2
while @e<@t
begin
if dbo.f_isNumsPrime(@e,@t)=0
begin
set @d=2
while @d<@n
begin
if(@e*@d%@t=1)
print cast(@e as varchar)+space(5)+cast(@d as varchar)
set @d=@d+1
end
end
set @e=@e+1
end
end
/*加密函数说明，@key 为上一个存储过程中选择的密码中的一个,@p ,@q 产生密钥对时选择的两个数。获取每一个字符的unicode值，然后进行加密，产生个字节的位数据*/
if object_id('f_RSAEncry') is not null
drop function f_RSAEncry
go
create function f_RSAEncry
(@s varchar(100),@key int ,@p int ,@q int)
returns nvarchar(4000)
as
begin
declare @crypt varchar(8000)
set @crypt=''
while len(@s)>0
begin
declare @i bigint,@tmp varchar(10),@k2 int,@leftchar int
select @leftchar=unicode(left(@s,1)),@k2=@key/2,@i=1
while @k2>0
begin
set @i=(cast(power(@leftchar,2) as bigint)*@i)%(@p*@q)
set @k2=@k2-1
end
set @i=(@leftchar*@i)%(@p*@q)
set @tmp=''
select @tmp=case when @i%16 between 10 and 15 then char( @i%16+55) else cast(@i%16 as varchar) end +@tmp,@i=@i/16
from (select number from master.dbo.spt_values where type='p' and number<10 )K
order by number desc
set @crypt=@crypt+right(@tmp,6)
set @s=stuff(@s,1,1,'')
end
return @crypt
end
--解密：@key 为一个存储过程中选择的密码对中另一个数字,@p ,@q 产生密钥对时选择的两个数
if object_id('f_RSADecry') is not null
drop function f_RSADecry
go
create function f_RSADecry
(@s nvarchar(4000),@key int ,@p int ,@q int)
returns nvarchar(4000)
as
begin
declare @crypt varchar(8000)
set @crypt=''
while len(@s)>0
begin
declare @leftchar bigint
select @leftchar=sum(data1)
from (select case upper(substring(left(@s,6), number, 1)) when 'A' then 10
when 'B' then 11
when 'C' then 12
when 'D' then 13
when 'E' then 14
when 'F' then 15
else substring(left(@s,6), number, 1)
end* power(16, len(left(@s,6)) - number) data1
from (select number from master.dbo.spt_values where type='p')K
where number <= len(left(@s,6))
) L
declare @k2 int,@j bigint
select @k2=@key/2,@j=1
while @k2>0
begin
set @j=(cast(power(@leftchar,2)as bigint)*@j)%(@p*@q)
set @k2=@k2-1
end
set @j=(@leftchar*@j)%(@p*@q)
set @crypt=@crypt+nchar(@j)
set @s=stuff(@s,1,6,'')
end
return @crypt
end
使用方法：
1、先使用p_createkey生成一对密钥，参数为两个参数
2、调用相应进行加密、解密
--【测试】
if object_id('tb') is not null
drop table tb
go
create table tb(id int identity(1,1),col varchar(100))
go
insert into tb values(dbo.f_RSAEncry('中国人',779,1163,59))
insert into tb values(dbo.f_RSAEncry('Chinese',779,1163,59))
select * from tb
--运行结果
/*
id col
----------- ---------------------------------------------
00359B00E6E000EAF5
01075300931B0010A4007EDC004B340074A6004B34
*/
select * ,解密后=dbo.f_RSADecry(col,35039,1163,59)from tb
--测试示例
/*
id col 解密后
----------- ------------------------------------------- -----------
00359B00E6E000EAF5 中国人
01075300931B0010A4007EDC004B340074A6004B34 Chinese
*/
53、输出指定格式的数据列
-- =============================================
-- Author: maco_wang
-- Create date: 2011-03-30
-- Description:
-- 需求贴：http://topic.csdn.net/u/20110330/10/dd155c82-e156-49df-9b5a-65bdbb0bf3ab.html
-- =============================================
前记：
Csdn上看到一帖子，要求如下：
编程一个函数实现功能，给出n,打印1-n,例如1 22 33 444 555 666 7777 8888 9999 10101010
就是要
1个1位： 1
2个2位： 22 33
3个3位： 444 555 666
4个4位： 7777 8888 9999 10101010
....
虽然是.NET技术-ASP.NET板块的帖子，但是思路都是一样的，用SQL写了一下：
create function PrintN(@n int)
returns @table table (id bigint)
as
begin
declare @i bigint;set @i=1
declare @j bigint;declare @k bigint;
while (@i<=@n)
begin
set @j=0;set @k=0
while @j<@i
begin
set @j=@j+@k;set @k=@k+1
end
insert into @table select replicate(@i,@k-1)
set @i=@i+1
end
return
end
--查看结果
select * from dbo.PrintN(20)
/*
*/
54、汉字转拼音函数
/* -------------------------------------------------------------
函数: fn_GetPinyin
描述: 汉字转拼音(无数据表版)
使用: dbo.fn_GetPinyin('×××') = zhonghuarenmingongheguo
作者: 流香羽(改编：Tony)
博客: http://hi.baidu.com/流香羽
------------------------------------------------------------- */
--创建函数
IF OBJECT_ID('[fn_GetPinyin]') IS NOT NULL
DROP FUNCTION [fn_GetPinyin]
GO
create function [dbo].[fn_GetPinyin](@words nvarchar(2000))
returns varchar(8000)
as
begin
declare @word nchar(1)
declare @pinyin varchar(8000)
declare @i int
declare @words_len int
declare @unicode int
set @i = 1
set @words = ltrim(rtrim(@words))
set @words_len = len(@words)
while (@i <= @words_len) --循环取字符
begin
set @word = substring(@words, @i, 1)
set @unicode = unicode(@word)
set @pinyin = ISNULL(@pinyin +SPACE(1),'')+
(case when unicode(@word) between 19968 and 19968+20901 then
(select top 1 py from (
select 'a' as py,N'厑' as word
union all select 'ai',N'靉'
union all select 'an',N'黯'
union all select 'ang',N'醠'
union all select 'ao',N'驁'
union all select 'ba',N'欛'
union all select 'bai',N'瓸' --韛兡瓸
union all select 'ban',N'瓣'
union all select 'bang',N'鎊'
union all select 'bao',N'鑤'
union all select 'bei',N'鐾'
union all select 'ben',N'輽'
union all select 'beng',N'鏰'
union all select 'bi',N'鼊'
union all select 'bian',N'變'
union all select 'biao',N'鰾'
union all select 'bie',N'彆'
union all select 'bin',N'鬢'
union all select 'bing',N'靐'
union all select 'bo',N'蔔'
union all select 'bu',N'簿'
union all select 'ca',N'囃'
union all select 'cai',N'乲' --縩乲
union all select 'can',N'爘'
union all select 'cang',N'賶'
union all select 'cao',N'鼜'
union all select 'ce',N'簎'
union all select 'cen',N'笒'
union all select 'ceng',N'乽' --硛硳岾猠乽
union all select 'cha',N'詫'
union all select 'chai',N'囆'
union all select 'chan',N'顫'
union all select 'chang',N'韔'
union all select 'chao',N'觘'
union all select 'che',N'爡'
union all select 'chen',N'讖'
union all select 'cheng',N'秤'
union all select 'chi',N'鷘'
union all select 'chong',N'銃'
union all select 'chou',N'殠'
union all select 'chu',N'矗'
union all select 'chuai',N'踹'
union all select 'chuan',N'鶨'
union all select 'chuang',N'愴'
union all select 'chui',N'顀'
union all select 'chun',N'蠢'
union all select 'chuo',N'縒'
union all select 'ci',N'嗭' --賜嗭
union all select 'cong',N'謥'
union all select 'cou',N'輳'
union all select 'cu',N'顣'
union all select 'cuan',N'爨'
union all select 'cui',N'臎'
union all select 'cun',N'籿'
union all select 'cuo',N'錯'
union all select 'da',N'橽'
union all select 'dai',N'靆'
union all select 'dan',N'饏'
union all select 'dang',N'闣'
union all select 'dao',N'纛'
union all select 'de',N'的'
union all select 'den',N'扽'
union all select 'deng',N'鐙'
union all select 'di',N'螮'
union all select 'dia',N'嗲'
union all select 'dian',N'驔'
union all select 'diao',N'鑃'
union all select 'die',N'嚸' --眰嚸
union all select 'ding',N'顁'
union all select 'diu',N'銩'
union all select 'dong',N'霘'
union all select 'dou',N'鬭'
union all select 'du',N'蠹'
union all select 'duan',N'叾' --籪叾
union all select 'dui',N'譵'
union all select 'dun',N'踲'
union all select 'duo',N'鵽'
union all select 'e',N'鱷'
union all select 'en',N'摁'
union all select 'eng',N'鞥'
union all select 'er',N'樲'
union all select 'fa',N'髮'
union all select 'fan',N'瀪'
union all select 'fang',N'放'
union all select 'fei',N'靅'
union all select 'fen',N'鱝'
union all select 'feng',N'覅'
union all select 'fo',N'梻'
union all select 'fou',N'鴀'
union all select 'fu',N'猤' --鰒猤
union all select 'ga',N'魀'
union all select 'gai',N'瓂'
union all select 'gan',N'灨'
union all select 'gang',N'戇'
union all select 'gao',N'鋯'
union all select 'ge',N'獦'
union all select 'gei',N'給'
union all select 'gen',N'搄'
union all select 'geng',N'堩' --亙堩啹喼嗰
union all select 'gong',N'兣' --熕贑兝兣
union all select 'gou',N'購'
union all select 'gu',N'顧'
union all select 'gua',N'詿'
union all select 'guai',N'恠'
union all select 'guan',N'鱹'
union all select 'guang',N'撗'
union all select 'gui',N'鱥'
union all select 'gun',N'謴'
union all select 'guo',N'腂'
union all select 'ha',N'哈'
union all select 'hai',N'饚'
union all select 'han',N'鶾'
union all select 'hang',N'沆'
union all select 'hao',N'兞'
union all select 'he',N'靏'
union all select 'hei',N'嬒'
union all select 'hen',N'恨'
union all select 'heng',N'堼' --堼囍
union all select 'hong',N'鬨'
union all select 'hou',N'鱟'
union all select 'hu',N'鸌'
union all select 'hua',N'蘳'
union all select 'huai',N'蘾'
union all select 'huan',N'鰀'
union all select 'huang',N'鎤'
union all select 'hui',N'顪'
union all select 'hun',N'諢'
union all select 'huo',N'夻'
union all select 'ji',N'驥'
union all select 'jia',N'嗧'
union all select 'jian',N'鑳'
union all select 'jiang',N'謽'
union all select 'jiao',N'釂'
union all select 'jie',N'繲'
union all select 'jin',N'齽'
union all select 'jing',N'竸'
union all select 'jiong',N'蘔'
union all select 'jiu',N'欍'
union all select 'ju',N'爠'
union all select 'juan',N'羂'
union all select 'jue',N'钁'
union all select 'jun',N'攈'
union all select 'ka',N'鉲'
union all select 'kai',N'乫' --鎎乫
union all select 'kan',N'矙'
union all select 'kang',N'閌'
union all select 'kao',N'鯌'
union all select 'ke',N'騍'
union all select 'ken',N'褃'
union all select 'keng',N'鏗' --巪乬唟厼怾
union all select 'kong',N'廤'
union all select 'kou',N'鷇'
union all select 'ku',N'嚳'
union all select 'kua',N'骻'
union all select 'kuai',N'鱠'
union all select 'kuan',N'窾'
union all select 'kuang',N'鑛'
union all select 'kui',N'鑎'
union all select 'kun',N'睏'
union all select 'kuo',N'穒'
union all select 'la',N'鞡'
union all select 'lai',N'籟'
union all select 'lan',N'糷'
union all select 'lang',N'唥'
union all select 'lao',N'軂'
union all select 'le',N'餎'
union all select 'lei',N'脷' --嘞脷
union all select 'leng',N'睖'
union all select 'li',N'瓈'
union all select 'lia',N'倆'
union all select 'lian',N'纞'
union all select 'liang',N'鍄'
union all select 'liao',N'瞭'
union all select 'lie',N'鱲'
union all select 'lin',N'轥' --轥拎
union all select 'ling',N'炩'
union all select 'liu',N'咯' --瓼甅囖咯
union all select 'long',N'贚'
union all select 'lou',N'鏤'
union all select 'lu',N'氇'
union all select 'lv',N'鑢'
union all select 'luan',N'亂'
union all select 'lue',N'擽'
union all select 'lun',N'論'
union all select 'luo',N'鱳'
union all select 'ma',N'嘛'
union all select 'mai',N'霢'
union all select 'man',N'蘰'
union all select 'mang',N'蠎'
union all select 'mao',N'唜'
union all select 'me',N'癦' --癦呅
union all select 'mei',N'嚜'
union all select 'men',N'們'
union all select 'meng',N'霥' --霿踎
union all select 'mi',N'羃'
union all select 'mian',N'麵'
union all select 'miao',N'廟'
union all select 'mie',N'鱴' --鱴瓱
union all select 'min',N'鰵'
union all select 'ming',N'詺'
union all select 'miu',N'謬'
union all select 'mo',N'耱' --耱乮
union all select 'mou',N'麰' --麰蟱
union all select 'mu',N'旀'
union all select 'na',N'魶'
union all select 'nai',N'錼'
union all select 'nan',N'婻'
union all select 'nang',N'齉'
union all select 'nao',N'臑'
union all select 'ne',N'呢'
union all select 'nei',N'焾' --嫩焾
union all select 'nen',N'嫩'
union all select 'neng',N'能' --莻嗯鈪銰啱
union all select 'ni',N'嬺'
union all select 'nian',N'艌'
union all select 'niang',N'釀'
union all select 'niao',N'脲'
union all select 'nie',N'钀'
union all select 'nin',N'拰'
union all select 'ning',N'濘'
union all select 'niu',N'靵'
union all select 'nong',N'齈'
union all select 'nou',N'譳'
union all select 'nu',N'搙'
union all select 'nv',N'衄'
union all select 'nue',N'瘧'
union all select 'nuan',N'燶' --硸黁燶郍
union all select 'nuo',N'桛'
union all select 'o',N'鞰' --毮夞乯鞰
union all select 'ou',N'漚'
union all select 'pa',N'袙'
union all select 'pai',N'磗' --鎃磗
union all select 'pan',N'鑻'
union all select 'pang',N'胖'
union all select 'pao',N'礮'
union all select 'pei',N'轡'
union all select 'pen',N'喯'
union all select 'peng',N'喸' --浌巼闏乶喸
union all select 'pi',N'鸊'
union all select 'pian',N'騙'
union all select 'piao',N'慓'
union all select 'pie',N'嫳'
union all select 'pin',N'聘'
union all select 'ping',N'蘋'
union all select 'po',N'魄'
union all select 'pou',N'哛' --兺哛
union all select 'pu',N'曝'
union all select 'qi',N'蟿'
union all select 'qia',N'髂'
union all select 'qian',N'縴'
union all select 'qiang',N'瓩' --羻兛瓩
union all select 'qiao',N'躈'
union all select 'qie',N'籡'
union all select 'qin',N'藽'
union all select 'qing',N'櫦'
union all select 'qiong',N'瓗'
union all select 'qiu',N'糗'
union all select 'qu',N'覻'
union all select 'quan',N'勸'
union all select 'que',N'礭'
union all select 'qun',N'囕'
union all select 'ran',N'橪'
union all select 'rang',N'讓'
union all select 'rao',N'繞'
union all select 're',N'熱'
union all select 'ren',N'餁'
union all select 'reng',N'陾'
union all select 'ri',N'馹'
union all select 'rong',N'穃'
union all select 'rou',N'嶿'
union all select 'ru',N'擩'
union all select 'ruan',N'礝'
union all select 'rui',N'壡'
union all select 'run',N'橍' --橍挼
union all select 'ruo',N'鶸'
union all select 'sa',N'栍' --櫒栍
union all select 'sai',N'虄' --簺虄
union all select 'san',N'閐'
union all select 'sang',N'喪'
union all select 'sao',N'髞'
union all select 'se',N'飋' --裇聓
union all select 'sen',N'篸'
union all select 'seng',N'縇' --閪縇
union all select 'sha',N'霎'
union all select 'shai',N'曬'
union all select 'shan',N'鱔'
union all select 'shang',N'緔'
union all select 'shao',N'潲'
union all select 'she',N'欇'
union all select 'shen',N'瘮'
union all select 'sheng',N'賸'
union all select 'shi',N'瓧' --鰘齛兙瓧
union all select 'shou',N'鏉'
union all select 'shu',N'虪'
union all select 'shua',N'誜'
union all select 'shuai',N'卛'
union all select 'shuan',N'腨'
union all select 'shuang',N'灀'
union all select 'shui',N'睡'
union all select 'shun',N'鬊'
union all select 'shuo',N'鑠'
union all select 'si',N'乺' --瀃螦乺
union all select 'song',N'鎹'
union all select 'sou',N'瘶'
union all select 'su',N'鷫'
union all select 'suan',N'算'
union all select 'sui',N'鐩'
union all select 'sun',N'潠'
union all select 'suo',N'蜶'
union all select 'ta',N'襨' --躢襨
union all select 'tai',N'燤'
union all select 'tan',N'賧'
union all select 'tang',N'燙'
union all select 'tao',N'畓' --討畓
union all select 'te',N'蟘'
union all select 'teng',N'朰' --霯唞朰
union all select 'ti',N'趯'
union all select 'tian',N'舚'
union all select 'tiao',N'糶'
union all select 'tie',N'餮'
union all select 'ting',N'乭' --濎乭
union all select 'tong',N'憅'
union all select 'tou',N'透'
union all select 'tu',N'鵵'
union all select 'tuan',N'褖'
union all select 'tui',N'駾'
union all select 'tun',N'坉'
union all select 'tuo',N'籜'
union all select 'wa',N'韤'
union all select 'wai',N'顡'
union all select 'wan',N'贎'
union all select 'wang',N'朢'
union all select 'wei',N'躛'
union all select 'wen',N'璺'
union all select 'weng',N'齆'
union all select 'wo',N'齷'
union all select 'wu',N'鶩'
union all select 'xi',N'衋'
union all select 'xia',N'鏬'
union all select 'xian',N'鼸'
union all select 'xiang',N'鱌'
union all select 'xiao',N'斆'
union all select 'xie',N'躞'
union all select 'xin',N'釁'
union all select 'xing',N'臖'
union all select 'xiong',N'敻'
union all select 'xiu',N'齅'
union all select 'xu',N'蓿'
union all select 'xuan',N'贙'
union all select 'xue',N'瀥'
union all select 'xun',N'鑂'
union all select 'ya',N'齾'
union all select 'yan',N'灩'
union all select 'yang',N'樣'
union all select 'yao',N'鑰'
union all select 'ye',N'岃' --鸈膶岃
union all select 'yi',N'齸'
union all select 'yin',N'檼'
union all select 'ying',N'譍'
union all select 'yo',N'喲'
union all select 'yong',N'醟'
union all select 'you',N'鼬'
union all select 'yu',N'爩'
union all select 'yuan',N'願'
union all select 'yue',N'鸙'
union all select 'yun',N'韻'
union all select 'za',N'雥'
union all select 'zai',N'縡'
union all select 'zan',N'饡'
union all select 'zang',N'臟'
union all select 'zao',N'竈'
union all select 'ze',N'稄'
union all select 'zei',N'鱡'
union all select 'zen',N'囎'
union all select 'zeng',N'贈'
union all select 'zha',N'醡'
union all select 'zhai',N'瘵'
union all select 'zhan',N'驏'
union all select 'zhang',N'瞕'
union all select 'zhao',N'羄'
union all select 'zhe',N'鷓'
union all select 'zhen',N'黮'
union all select 'zheng',N'證'
union all select 'zhi',N'豒'
union all select 'zhong',N'諥'
union all select 'zhou',N'驟'
union all select 'zhu',N'鑄'
union all select 'zhua',N'爪'
union all select 'zhuai',N'跩'
union all select 'zhuan',N'籑'
union all select 'zhuang',N'戅'
union all select 'zhui',N'鑆'
union all select 'zhun',N'稕'
union all select 'zhuo',N'籱'
union all select 'zi',N'漬' --漬唨
union all select 'zong',N'縱'
union all select 'zou',N'媰'
union all select 'zu',N'謯'
union all select 'zuan',N'攥'
union all select 'zui',N'欈'
union all select 'zun',N'銌'
union all select 'zuo',N'咗') t
where word >= @word collate Chinese_PRC_CS_AS_KS_WS
order by word collate Chinese_PRC_CS_AS_KS_WS ASC) else @word end)
set @i = @i + 1
end
return @pinyin
END
GO
--测试示例
SELECT dbo.fn_GetPinyin('欢迎访问叶子的博客')
--运行结果
/*
huan ying fang wen ye zi de bo ke
*/
55、数字转IP地址函数
---------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-06-19 10:34:31
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog : http://blog.csdn.net/htl258
-- Subject: 数字转IP地址函数
---------------------------------------------------------------------
--数字转IP地址函数：
IF OBJECT_ID('dbo.fn_IP2Str')>0
DROP FUNCTION dbo.fn_IP2Str
GO
CREATE FUNCTION [dbo].[fn_IP2Str] (
@InIP BIGINT
)
RETURNS NVARCHAR(15)
AS
BEGIN
IF @InIP IS NULL
RETURN '0.0.0.0'
DECLARE @ip BIGINT
SET @ip = @InIP
SET @ip = @ip + 0x100000000
RETURN
CAST(((@ip & 0xff000000) / 0x1000000) AS NVARCHAR(3)) + '.' +
CAST(((@ip & 0xff0000) / 0x10000) AS NVARCHAR(3)) + '.' +
CAST(((@ip & 0xff00) / 0x100) AS NVARCHAR(3)) + '.' +
CAST((@ip & 0xff) AS NVARCHAR(3))
END
GO
--调用示例：
SELECT dbo.fn_IP2Str(4294967295)
/*
---------------
255.255.255.255
(1 行受影响)
*/
SELECT dbo.fn_IP2Str(0)
/*
---------------
0.0.0.0
(1 行受影响)
*/
--附：以下转自邹建
--ip地址与数字相互转换的sql函数
IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[f_IP2Int]')
AND xtype IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[f_IP2Int]
GO
/*--字符型IP 地址转换成数字IP
--邹建 2004.08(引用请保留此信息)--*/
/*--调用示例
select dbo.f_IP2Int('255.255.255.255')
select dbo.f_IP2Int('12.168.0.1')
--*/
CREATE FUNCTION f_IP2Int
(
@ip CHAR(15)
)
RETURNS BIGINT
AS
BEGIN
DECLARE @re BIGINT
SET @re = 0
SELECT @re = @re+LEFT(@ip, CHARINDEX('.', @ip+'.')-1)*ID, @ip = STUFF(@ip, 1, CHARINDEX('.', @ip+'.'), '')
FROM (
SELECT ID = CAST(16777216 AS BIGINT)
UNION ALL SELECT 65536
UNION ALL SELECT 256
UNION ALL SELECT 1
)A
RETURN(@re)
END
GO
IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[f_Int2IP]')
AND xtype IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[f_Int2IP]
GO
/*--数字 IP 转换成格式化 IP 地址
--邹建 2004.08(引用请保留此信息)--
*/
/*--调用示例
select dbo.f_Int2IP(4294967295)
select dbo.f_Int2IP(212336641)
--*/
CREATE FUNCTION f_Int2IP
(
@IP BIGINT
)
RETURNS VARCHAR(15)
AS
BEGIN
DECLARE @re VARCHAR(16)
SET @re = ''
SELECT @re = @re+'.'+CAST(@IP/ID AS VARCHAR), @IP = @IP%ID
FROM (
SELECT ID = CAST(16777216 AS BIGINT)
UNION ALL SELECT 65536
UNION ALL SELECT 256
UNION ALL SELECT 1
)a
RETURN(STUFF(@re, 1, 1, ''))
END
GO
select dbo.f_Int2IP(333444343)
/*
19.223.244.247
*/
56、对字符串进行加密解密
create view v_rand
as
select c=unicode(cast(round(rand()*255,0) as tinyint))
go
create function f_jmstr
(
@str varchar(8000),
@type bit
)
returns varchar(8000)
/*
*参数说明
*str:要加密的字符串或已经加密后的字符
*type:操作类型--0加密--解密
*返回值说明
*当操作类型为加密时(type--0):返回为加密后的str,即存放于数据库中的字符串
*当操作类型为解密时(type--1):返回为实际字符串,即加密字符串解密后的原来字符串
*/
As
begin
declare @re varchar(8000)--返回值
declare @c int--加密字符
declare @i int
/*
*加密方法为原字符异或一个随机ASCII字符
*/
if @type=0--加密
begin
select @c=c,@re='',@i=len(@str) from v_rand
while @i>0
select @re=nchar(unicode(substring(@str,@i,1))^@c^@i)+@re
,@i=@i-1
set @re=@re+nchar(@c)
end
else--解密
begin
select @i=len(@str)-1,@c=unicode(substring(@str,@i+1,1)),@re=''
while @i>0
select @re=nchar(unicode(substring(@str,@i,1))^@c^@i)+@re ,@i=@i-1
end
return(@re)
end
go
--测试
declare @tempstr varchar(20)
set @tempstr=' 1 2 3aA'
select '原始值:',@tempstr
select '加密后:',dbo.f_jmstr(@tempstr,0)
select '解密后:',dbo.f_jmstr(dbo.f_jmstr(@tempstr,0),1)
--输出结果
/*
原始值: 1 2 3aA
加密后: __0'15`'17__°{1
解密后: 1 2 3aA
*/
本文来自CSDN博客，转载请标明出处：
http://blog.csdn.net/callzjy/archive/2004/05/21/20071.aspx
57、计算个人所得税函数
-- =============================================
-- Author: Maco_wang
-- Create date: 2011-03-<Create Date,,>
-- Description: 参考htl258(Tony)的思路，改写的计算个税的函数
-- =============================================
create function TaxRateOfPersonal
(
@fvalue numeric(18,4)
)
returns numeric(18,4)
as
begin
declare @i numeric(18,4)
declare @basetable table(id int,
basemoney numeric(18,4),minvalue numeric(18,4),
maxvalue numeric(18,4),taxs numeric(18,4))
insert into @basetable
select 1,2000,0,1000,0.05 union all
select 2,2000,1000,3000,0.1 union all
select 3,2000,3000,6000,0.15 union all
select 4,2000,6000,10000,0.2 union all
select 5,2000,10000,15000,0.25
select @i=sum(case when @fvalue>basemoney+maxvalue
then maxvalue-minvalue else @fvalue-basemoney-minvalue end *taxs)
from @basetable where basemoney+minvalue<=@fvalue
return @i
end
--测试示例
select dbo.TaxRateOfPersonal(2500)
select dbo.TaxRateOfPersonal(3500)
select dbo.TaxRateOfPersonal(5000)
select dbo.TaxRateOfPersonal(9500)
--运行结果
/*
25.0000
100.0000
250.0000
1000.0000
*/
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
