Microsoft SQL Server 自定义函数整理大全（下）
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 ,
@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
-- 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
-- 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 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、求字符串中汉字的个数 38、得到条形码的校验位函数 二、SQL实现： go -- ============================================= -- Author: -- 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); if(@code13=10) set @code13=0 return @ActiveCode+cast(@code13 as varchar(1)); end --测试示例 select dbo.[Get_CheckCode]('827123121212') --运行结果 /* 8271231212124 */ 39、根据年月得到当月的天数 40、将一个正整数分解为m个2的n次方的和 -- ============================================= -- Author: -- 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位移运算函数 42、得到汉字笔画函数 43、SQL数字转英文函数 --(此函数部分思路参考了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 */ 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替换逗号，求长度差就可以了，但是这里这个函数两个逗号相邻做了处理。 */ 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 （无枪狙击手）也写过一个，参数上做了扩展，可以定义分隔符了，在【叶子函数分享十六】我曾经发过。 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) */ --改写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) */ 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', ',') --运行结果 /* */ /*************************************************** 作者：herowang(让你望见影子的墙） 日期：2010.1.1 注： 转载请保留此信息 更多内容，请访问我的博客：blog.csdn.net/herowang ****************************************************/ 一、RSA算法原理 RSA算法非常简单，概述如下： 找两素数p和q 取n=p*q 取t=(p-1)*(q-1) 取任何一个数e,要求满足e 取d*e%t==1 这样最终得到三个数：n d e 设消息为数M (M 设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 between 10 and 15 then char( @i+55) else cast(@i 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、如果选取的数字比较大，那么在进行加密的时候，生成的进制密文最好使用个字节或者更多。 /*************************************************** 作者：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 between 10 and 15 then char( @i+55) else cast(@i 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、输出指定格式的数据列 -- ============================================= /* ------------------------------------------------------------- 函数: 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 */ --------------------------------------------------------------------- -- 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 -- 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 */ 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- -- 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 */一、分解字符串法首先创建这个函数： /*将字符串分解*/create function [dbo].[SplitChar] ( @str_One Nvarchar(100) )returns @Result table (col nvarchar(1))ASBEGIN 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 returnEND 然后创建如下函数/*求字符串中汉字个数*/Create function [dbo].[ChineseCountOne]( @Str_One nvarchar(200))RETURNS int ASBEGIN declare @number_One int SELECT @number_One =count(*) from dbo.SplitChar(@Str_One) where ascii(col)>127 return @number_OneEND /*使用示例select dbo.[ChineseCountOne] ('China中国Beijing北京Olympics奥林匹克')*/ 二、字符字节求差法首先创建这个函数：create function [dbo].[Chinesecount_Two]( @Str_One varchar(200))RETURNS int ASBEGIN declare @number_One int set @number_One=(datalength(@Str_One)-len(@Str_One)) return @number_OneEND /*使用示例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' --此例说明此方法不受数据类型限制*/go--创建函数create function [dbo].[get_days]( @yearmonth varchar(6) --参数格式：)returns intasbegin return (datediff(d,@yearmonth+'01',dateadd(mm,1,@yearmonth+'01')))end --测试示例select dbo.[get_days]('201103') --运行结果/**/-- =============================================-- Author:
--===============================================--功能：汉字笔画函数--说明：以单个汉字汉字为参数返回每一个汉字的笔画数--作者: J9988 --*/--===============================================create function [dbo].[fun_getbh](@char nchar(2))returns intasbeginreturn( 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('晓') --运行结果/**/--晴天兄(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)ASBEGIN 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 @englishENDGO -- =============================================-- 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 CALLERASBEGIN 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 @englishENDGO 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 oneone hundred and twenty trillion forty five billion eighty million forty five thousand fifty four point eight four one twozero point zero one zero two five four one*/
44、全角半角转换函数
45、返回两个时间范围内的一个随机时间/******************************* 函数名：RandDateTime* 作用： 返回两个时间范围内的一个随机时间* Author: 兰习刚* Date: 2009-11-30*******************************/go--创建函数create Function RandDateTime( @RandNum Decimal(38,18),--0-1之际随机数值建议Rand() @StartTime DateTime, --第一个时间 @EndTime DateTime --第二个时间)Returns DateTimeAsBegin 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 @ReturnDateTimeEnd --测试示例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、获取元素个数的函数
47、获取指定索引的值的函数
48、根据年得到所有星期日的日期
49、生成两个时间之间的所有日期
50、无序字符比较函数
51、在SQL SERVER中实现RSA加解密函数（第一版）
52、在SQL SERVER中实现RSA加解密函数（第二版）
-- 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、汉字转拼音函数
55、数字转IP地址函数
56、对字符串进行加密解密
