溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
34、字符串轉(zhuǎn)成16進(jìn)制函數(shù)
/****************************
字符串轉(zhuǎn)成16進(jìn)制
作者：不得閑
QQ: 75492895
Email: appleak46@yahoo.com.cn
****************************/
--創(chuàng)建函數(shù)(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
--測(cè)試示例
select dbo.VarCharToHex('葉子')
--運(yùn)行結(jié)果
/*
D2B6D7D3
*/
35、去掉字段中多個(gè)帶區(qū)號(hào)電話號(hào)碼前面的區(qū)號(hào)
--原帖地址：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: 應(yīng)用實(shí)例：去掉字段中多個(gè)帶區(qū)號(hào)電話號(hào)碼前面的區(qū)號(hào)
--------------------------------------------------------------------
--需求貼：http://topic.csdn.net/u/20100428/20/f2572998-099c-463a-a530-707a40606c9c.html?53227
--> 生成測(cè)試數(shù)據(jù)表: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查詢?nèi)缦?
--1.創(chuàng)建自定義函數(shù)
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博客
--轉(zhuǎn)載請(qǐng)標(biāo)明出處：--http://blog.csdn.net/htl258/archive/2010/04/28/5540795.aspx
36、SQL2000/2005字符串拆分為列表通用函數(shù)
-- 原帖地址：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字符串拆分為列表通用函數(shù)
-------------------------------------------------------------------
--SQL2000/2005字符串拆分為列表通用函數(shù)
IF OBJECT_ID('f_getstr') IS NOT NULL
DROP FUNCTION f_getstr
GO
CREATE FUNCTION f_getstr(
@s NVARCHAR(4000), --待分拆的字符串
@flag NVARCHAR(10)='' --數(shù)據(jù)分隔符
)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
--本實(shí)例技巧，利用master庫自帶的spt_values表，取number字段作為連續(xù)的序號(hào)，
--省去創(chuàng)建序號(hào)表，盡量做到通用，再加上字符串處理函數(shù)取得最終結(jié)果。
--1.每個(gè)字符拆分取出
SELECT * FROM dbo.f_getstr(N'一個(gè)世界一個(gè)家',NULL)
SELECT * FROM dbo.f_getstr(N'一個(gè)世界一個(gè)家','')
SELECT * FROM dbo.f_getstr(N'一個(gè)世界一個(gè)家',default)
/*
col
-------
一
個(gè)
世
界
一
個(gè)
家
(7 行受影響)
*/
--2.指定分隔符拆分取出
SELECT * FROM dbo.f_getstr(N'一個(gè)世界一個(gè)家',N' ')
SELECT * FROM dbo.f_getstr(N'一個(gè),世界,一個(gè),家',N',')
SELECT * FROM dbo.f_getstr(N'一個(gè)%世界%一個(gè)%家',N'%')
SELECT * FROM dbo.f_getstr(N'一個(gè)中國世界中國一個(gè)中國家',N'中國')
/*
col
---------
一個(gè)
世界
一個(gè)
家
(4 行受影響)
*/
--3.SQL2005以上版本可以結(jié)合apply進(jìn)行拆分列值
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博客
--轉(zhuǎn)載請(qǐng)標(biāo)明出處：
--http://blog.csdn.net/htl258/archive/2010/04/28/5537235.aspx
37、求字符串中漢字的個(gè)數(shù)
一、分解字符串法
首先創(chuàng)建這個(gè)函數(shù)：
/*將字符串分解*/
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
然后創(chuàng)建如下函數(shù)
/*求字符串中漢字個(gè)數(shù)*/
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奧林匹克')
*/
二、字符字節(jié)求差法
首先創(chuàng)建這個(gè)函數(shù)：
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) '個(gè)數(shù)one' ,
dbo.[Chinesecount_Two] (@sql_two) '個(gè)數(shù)two'
--此例說明此方法不受數(shù)據(jù)類型限制
*/38、得到條形碼的校驗(yàn)位函數(shù)
二、SQL實(shí)現(xiàn)：
go
-- =============================================
-- Author: <Maco Wang>
-- Create date: <2009-10-27 16:01>
-- Description: <計(jì)算條形碼的校驗(yàn)碼>
-- =============================================
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
--測(cè)試示例
select dbo.[Get_CheckCode]('827123121212')
--運(yùn)行結(jié)果
/*
8271231212124
*/
39、根據(jù)年月得到當(dāng)月的天數(shù)
go
--創(chuàng)建函數(shù)
create function [dbo].[get_days]
(
@yearmonth varchar(6) --參數(shù)格式：
)
returns int
as
begin
return (datediff(d,@yearmonth+'01',dateadd(mm,1,@yearmonth+'01')))
end
--測(cè)試示例
select dbo.[get_days]('201103')
--運(yùn)行結(jié)果
/*
*/40、將一個(gè)正整數(shù)分解為m個(gè)2的n次方的和
-- =============================================
-- Author: <maco_wang>
-- Create date: <2011-01-21>
-- Description: <將一個(gè)正整數(shù)分解為m個(gè)2的n次方的和>
-- =============================================
go
--創(chuàng)建函數(shù)
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
--測(cè)試示例
select dbo.GetSumSequence(12)
select dbo.GetSumSequence(65)
select dbo.GetSumSequence(892)
select dbo.GetSumSequence(1919191)
--運(yùn)行結(jié)果
/*
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位移運(yùn)算函數(shù)
-- =============================================
-- Author: <maco_wang>
-- Create date: <2011-03-22>
-- Description: <SQL位移運(yùn)算函數(shù)>
-- =============================================
go
--創(chuàng)建函數(shù)
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
--測(cè)試示例
select dbo.displacement(1,'<<3')
select dbo.displacement(8,'>>1')
select dbo.displacement(12,'>>3')
--運(yùn)行結(jié)果
/*
*/42、得到漢字筆畫函數(shù)
--===============================================
--功能：漢字筆畫函數(shù)
--說明：以單個(gè)漢字漢字為參數(shù)返回每一個(gè)漢字的筆畫數(shù)
--作者: 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'風(fēng)'
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
--測(cè)試示例
select dbo.fun_getbh('曉')
--運(yùn)行結(jié)果
/*
*/43、SQL數(shù)字轉(zhuǎn)英文函數(shù)
--晴天兄(qianjin036a)的發(fā)帖地址： --http://topic.csdn.net/u/20080614/12/d26adea8-ac05-4b06-8b8a-f46a4b564e3b.html -- 數(shù)字轉(zhuǎn)英文 -- ============================================= -- Author: qianjin036a -- Create date:06/14/2008 02:27:17 -- Description:Arabic numerals to English -- ============================================= go --創(chuàng)建函數(shù) 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、全角半角轉(zhuǎn)換函數(shù)
--(此函數(shù)部分思路參考了CSDN上大力的轉(zhuǎn)換函數(shù))
--鄒建2005.01(引用請(qǐng)保留此信息)--*/
go
--創(chuàng)建函數(shù)
create function SBC2DBC
(
@str nvarchar(4000), --要轉(zhuǎn)換的字符串
@flag bit --轉(zhuǎn)換標(biāo)志,0轉(zhuǎn)換成半角,1轉(zhuǎn)換成全角
)
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
--測(cè)試示例
select dbo.SBC2DBC('~~~~ca!b',1)
--運(yùn)行結(jié)果
/*
～～～～ｃａ?。?/p>
*/
--附半角全角表
/*
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(xiǎn)+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、返回兩個(gè)時(shí)間范圍內(nèi)的一個(gè)隨機(jī)時(shí)間
/******************************
* 函數(shù)名：RandDateTime
* 作用： 返回兩個(gè)時(shí)間范圍內(nèi)的一個(gè)隨機(jī)時(shí)間
* Author: 蘭習(xí)剛
* Date: 2009-11-30
*******************************/
go
--創(chuàng)建函數(shù)
create Function RandDateTime
(
@RandNum Decimal(38,18),--0-1之際隨機(jī)數(shù)值建議Rand()
@StartTime DateTime, --第一個(gè)時(shí)間
@EndTime DateTime --第二個(gè)時(shí)間
)
Returns DateTime
As
Begin
Declare @HourDiff Decimal(38,18) --兩個(gè)時(shí)間之間的小時(shí)差值
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) --取較小的時(shí)間
Set @HourDiff = Abs(@HourDiff)
ActionLable:
Declare @HourDecimal Decimal(38,18) --小時(shí)的小數(shù)部分
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) --獲得小時(shí)的小數(shù)部分
Set @MsPartDiff = (@MsPartDiff + @HourDecimal * 3600*1000) * @RandNum
/*毫秒差值
由于之前@MsPartDiff是兩個(gè)時(shí)間小時(shí)之后的毫秒差值
@HourDecimal * 3600*1000 有小時(shí)的小數(shù)部分的毫秒差值不會(huì)大于小時(shí)
毫秒不會(huì)溢出
*/
Set @ReturnDateTime = DateAdd(hh,@HourDiff,@SmallDate)
Set @ReturnDateTime = DateAdd(ms,@MsPartDiff,@ReturnDateTime)
Return @ReturnDateTime
End
--測(cè)試示例
select dbo.RandDateTime(Rand(),'2011-03-21 00:00:00.000','2011-03-21 23:59:00.000')
go 10
--運(yùn)行結(jié)果
/*
-----------------------
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、獲取元素個(gè)數(shù)的函數(shù)
go
-- 創(chuàng)建函數(shù)(作者：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
-- 測(cè)試示例
SELECT [dbo].[getstrarrlength]('1,2,3,4,a,b,c,d')
--運(yùn)行結(jié)果
/*
*/
/*
說明：
我開始考慮直接看逗號(hào)的個(gè)數(shù)，用replace替換逗號(hào)，求長度差就可以了，但是這里這個(gè)函數(shù)兩個(gè)逗號(hào)相鄰做了處理。
*/
47、獲取指定索引的值的函數(shù)
go
--創(chuàng)建函數(shù)(作者：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 --如果習(xí)慣從開始則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 --如果是因?yàn)闆]有逗號(hào)退出，則認(rèn)為逗號(hào)在字符串后
select @str_return = substring(@str,@start,@location -@start) --@start肯定是逗號(hào)之后的位置或者就是初始值
if (@index <> @next ) select @str_return = '' --如果二者不相等，則是因?yàn)槎禾?hào)太少，或者@index小于@next的初始值。
return @str_return
end
--測(cè)試示例
SELECT [dbo].[getstrofindex]('1,2,3,4,a,b,c,d',4)
--運(yùn)行結(jié)果
/*
*/
--備注：類似功能的函數(shù)happyflystone （無槍狙擊手）也寫過一個(gè)，參數(shù)上做了擴(kuò)展，可以定義分隔符了，在【葉子函數(shù)分享十六】我曾經(jīng)發(fā)過。
48、根據(jù)年得到所有星期日的日期
go
--創(chuàng)建函數(shù)
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
--測(cè)試示例
select * from dbo.GetWeekDays(2011)
--運(yùn)行結(jié)果
/*
星期天
--------------------
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、生成兩個(gè)時(shí)間之間的所有日期
--改寫liangCK的部分代碼為函數(shù)
--創(chuàng)建函數(shù)
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
--測(cè)試示例
select * from dbo.generateTime('2009-01-01','2009-01-10')
--運(yùn)行結(jié)果
/*
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
--第二版
--創(chuàng)建函數(shù)
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
--測(cè)試示例
select * from dbo.generateTimeV2('2009-01-01','2009-01-10')
--運(yùn)行結(jié)果
/*
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、無序字符比較函數(shù)
go
--創(chuàng)建函數(shù)(第一版)
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
--測(cè)試示例
if(dbo.get_orderstr('abc')=dbo.get_orderstr('acb'))
print '相同'
else
print '不同'
--運(yùn)行結(jié)果
/*
相同
*/
--第二版
/*
* 功能：不按先后順序比較字符串序列是否相同 *
* 適用：SQL Server 2000 / SQL Server 2005 *
* 返回：相同不相同 *
* 作者：Flystone *
* 描述：學(xué)習(xí)Limpire(昨夜小樓)的方法后做一個(gè)動(dòng)態(tài)SQL的*
*/
go
--創(chuàng)建存儲(chǔ)過程(這個(gè)不是函數(shù))
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
--測(cè)試示例
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(昨夜小樓) *
* * * * * * * * * * * * * * * * * * * * * * */
--創(chuàng)建函數(shù)
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
--測(cè)試示例
select dbo.fn_CompareString('a,b,c', 'b,c,a', ',')
select dbo.fn_CompareString('a,b,c', 'b,c,c,a', ',')
--運(yùn)行結(jié)果
/*
*/
51、在SQL SERVER中實(shí)現(xiàn)RSA加解密函數(shù)（第一版）
/***************************************************
作者：herowang(讓你望見影子的墻）
日期：2010.1.1
注： 轉(zhuǎn)載請(qǐng)保留此信息
更多內(nèi)容，請(qǐng)?jiān)L問我的博客：blog.csdn.net/herowang
****************************************************/
一、RSA算法原理
RSA算法非常簡單，概述如下：
找兩素?cái)?shù)p和q
取n=p*q
取t=(p-1)*(q-1)
取任何一個(gè)數(shù)e,要求滿足e<t并且e與t互素（就是最大公因數(shù)為）
取d*e%t==1
這樣最終得到三個(gè)數(shù)：n d e
設(shè)消息為數(shù)M (M <n)
設(shè)c=(M**d)%n就得到了加密后的消息c
設(shè)m=(c**e)%n則m == M，從而完成對(duì)c的解密。
注：**表示次方,上面兩式中的d和e可以互換。
在對(duì)稱加密中：
n d兩個(gè)數(shù)構(gòu)成公鑰，可以告訴別人；
n e兩個(gè)數(shù)構(gòu)成私鑰，e自己保留，不讓任何人知道。
給別人發(fā)送的信息使用e加密，只要?jiǎng)e人能用d解開就證明信息是由你發(fā)送的，構(gòu)成了簽名機(jī)制。
別人給你發(fā)送信息時(shí)使用d加密，這樣只有擁有e的你能夠?qū)ζ浣饷堋?/p>
rsa的安全性在于對(duì)于一個(gè)大數(shù)n，沒有有效的方法能夠?qū)⑵浞纸鈴亩谝阎猲 d的情況下無法獲得e；同樣在已知n e的情況下無法求得d。
以上內(nèi)容出自原文出處http://www.xfocus.net/articles/200503/778.html
二、使用T-SQL實(shí)現(xiàn)RSA算法
--判斷是否為素?cái)?shù)
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
--判斷兩個(gè)數(shù)是否互素，首先要選取兩個(gè)互素的數(shù)
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
--產(chǎn)生密鑰對(duì)
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)+'不是素?cái)?shù)，請(qǐng)重新選擇數(shù)據(jù)'
return
end
if dbo.f_pnumtest(@q)=0
begin
print cast(@q as varchar)+'不是素?cái)?shù)，請(qǐng)重新選擇數(shù)據(jù)'
return
end
print '請(qǐng)從下列數(shù)據(jù)中選擇其中一對(duì)，作為密鑰'
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
/*加密函數(shù)說明，@key 為上一個(gè)存儲(chǔ)過程中選擇的密碼中的一個(gè),@p ,@q 產(chǎn)生密鑰對(duì)時(shí)選擇的兩個(gè)數(shù)。獲取每一個(gè)字符的ascii值，然后進(jìn)行加密，產(chǎn)生個(gè)字節(jié)的位數(shù)據(jù)*/
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 為一個(gè)存儲(chǔ)過程中選擇的密碼對(duì)中另一個(gè)數(shù)字,@p ,@q 產(chǎn)生密鑰對(duì)時(shí)選擇的兩個(gè)數(shù)
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中的使用
--【測(cè)試】
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
--運(yùn)行結(jié)果：
/*
id col
----------- ------------
069505EE02F3
*/
select id,col=dbo.f_RSADecry(col,847,47,59) from tb
--運(yùn)行結(jié)果：
/*
id col
----------- -----------
RSA
*/
四、目前版本函數(shù)的缺點(diǎn)
1、目前只能對(duì)ascii符號(hào)進(jìn)行加密，對(duì)unicode尚不支持。
2、在選取的素?cái)?shù)都比較小，所以密鑰空間比較小，而實(shí)際應(yīng)用中選取的素?cái)?shù)都會(huì)非常的大，不容易破解。但是對(duì)于一些基礎(chǔ)的加密還能夠使用。
3、如果一次加密覺得安全性不夠的話，可以進(jìn)行重復(fù)加密（即進(jìn)行多次加密），兩次的密鑰最好不相同。
例如：insert into tb values(dbo.f_RSAEncry(dbo.f_RSAEncry('RSA',63,47,59),23,11,17))
那么解密的時(shí)候，按照加密的逆序進(jìn)行解密：
select id,col=dbo.f_RSADecry(dbo.f_RSADecry(col,7,11,17),847,47,59)
from tb
4、如果選取的數(shù)字比較大，那么在進(jìn)行加密的時(shí)候，生成的進(jìn)制密文最好使用個(gè)字節(jié)或者更多。
52、在SQL SERVER中實(shí)現(xiàn)RSA加解密函數(shù)（第二版）
/***************************************************
作者：herowang(讓你望見影子的墻）
日期：2010.1.5
注： 轉(zhuǎn)載請(qǐng)保留此信息
更多內(nèi)容，請(qǐng)?jiān)L問我的博客：blog.csdn.net/herowang
****************************************************/
/*
本次修改增加了unicode的支持，但是加密后依然顯示為進(jìn)制數(shù)據(jù)，因?yàn)檫M(jìn)行RSA加密后所得到的unicode編碼是無法顯示的，所以密文依然采用進(jìn)制數(shù)據(jù)顯示。
需要特別注意：如果要對(duì)中文進(jìn)行加密，那么所選取的兩個(gè)素?cái)?shù)要比較大，兩個(gè)素?cái)?shù)的成績最好要大于，即大于unicode的最大編碼值
另外修改了第一個(gè)版本的部分函數(shù)名稱
*/
在SQL SERVER中實(shí)現(xiàn)RSA加密算法
--判斷是否為素?cái)?shù)
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
--測(cè)試示例：
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
--判斷兩個(gè)數(shù)是否互素
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
--產(chǎn)生密鑰對(duì)
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)+'不是素?cái)?shù)，請(qǐng)重新選擇數(shù)據(jù)'
return
end
if dbo.f_primeNumTest(@q)=0
begin
print cast(@q as varchar)+'不是素?cái)?shù)，請(qǐng)重新選擇數(shù)據(jù)'
return
end
print '請(qǐng)從下列數(shù)據(jù)中選擇其中一對(duì)，作為密鑰'
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
/*加密函數(shù)說明，@key 為上一個(gè)存儲(chǔ)過程中選擇的密碼中的一個(gè),@p ,@q 產(chǎn)生密鑰對(duì)時(shí)選擇的兩個(gè)數(shù)。獲取每一個(gè)字符的unicode值，然后進(jìn)行加密，產(chǎn)生個(gè)字節(jié)的位數(shù)據(jù)*/
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 為一個(gè)存儲(chǔ)過程中選擇的密碼對(duì)中另一個(gè)數(shù)字,@p ,@q 產(chǎn)生密鑰對(duì)時(shí)選擇的兩個(gè)數(shù)
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生成一對(duì)密鑰，參數(shù)為兩個(gè)參數(shù)
2、調(diào)用相應(yīng)進(jìn)行加密、解密
--【測(cè)試】
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
--運(yùn)行結(jié)果
/*
id col
----------- ---------------------------------------------
00359B00E6E000EAF5
01075300931B0010A4007EDC004B340074A6004B34
*/
select * ,解密后=dbo.f_RSADecry(col,35039,1163,59)from tb
--測(cè)試示例
/*
id col 解密后
----------- ------------------------------------------- -----------
00359B00E6E000EAF5 中國人
01075300931B0010A4007EDC004B340074A6004B34 Chinese
*/
53、輸出指定格式的數(shù)據(jù)列
-- =============================================
-- Author: maco_wang
-- Create date: 2011-03-30
-- Description:
-- 需求貼：http://topic.csdn.net/u/20110330/10/dd155c82-e156-49df-9b5a-65bdbb0bf3ab.html
-- =============================================
前記：
Csdn上看到一帖子，要求如下：
編程一個(gè)函數(shù)實(shí)現(xiàn)功能，給出n,打印1-n,例如1 22 33 444 555 666 7777 8888 9999 10101010
就是要
1個(gè)1位： 1
2個(gè)2位： 22 33
3個(gè)3位： 444 555 666
4個(gè)4位： 7777 8888 9999 10101010
....
雖然是.NET技術(shù)-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
--查看結(jié)果
select * from dbo.PrintN(20)
/*
*/
54、漢字轉(zhuǎn)拼音函數(shù)
/* -------------------------------------------------------------
函數(shù): fn_GetPinyin
描述: 漢字轉(zhuǎn)拼音(無數(shù)據(jù)表版)
使用: dbo.fn_GetPinyin('×××') = zhonghuarenmingongheguo
作者: 流香羽(改編：Tony)
博客: http://hi.baidu.com/流香羽
------------------------------------------------------------- */
--創(chuàng)建函數(shù)
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) --循環(huán)取字符
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'錯(cuò)'
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'願(yuà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'贈(zèng)'
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
--測(cè)試示例
SELECT dbo.fn_GetPinyin('歡迎訪問葉子的博客')
--運(yùn)行結(jié)果
/*
huan ying fang wen ye zi de bo ke
*/
55、數(shù)字轉(zhuǎn)IP地址函數(shù)
---------------------------------------------------------------------
-- 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: 數(shù)字轉(zhuǎn)IP地址函數(shù)
---------------------------------------------------------------------
--數(shù)字轉(zhuǎn)IP地址函數(shù)：
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
--調(diào)用示例：
SELECT dbo.fn_IP2Str(4294967295)
/*
---------------
255.255.255.255
(1 行受影響)
*/
SELECT dbo.fn_IP2Str(0)
/*
---------------
0.0.0.0
(1 行受影響)
*/
--附：以下轉(zhuǎn)自鄒建
--ip地址與數(shù)字相互轉(zhuǎn)換的sql函數(shù)
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 地址轉(zhuǎn)換成數(shù)字IP
--鄒建 2004.08(引用請(qǐng)保留此信息)--*/
/*--調(diào)用示例
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
/*--數(shù)字 IP 轉(zhuǎn)換成格式化 IP 地址
--鄒建 2004.08(引用請(qǐng)保留此信息)--
*/
/*--調(diào)用示例
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、對(duì)字符串進(jìn)行加密解密
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)
/*
*參數(shù)說明
*str:要加密的字符串或已經(jīng)加密后的字符
*type:操作類型--0加密--解密
*返回值說明
*當(dāng)操作類型為加密時(shí)(type--0):返回為加密后的str,即存放于數(shù)據(jù)庫中的字符串
*當(dāng)操作類型為解密時(shí)(type--1):返回為實(shí)際字符串,即加密字符串解密后的原來字符串
*/
As
begin
declare @re varchar(8000)--返回值
declare @c int--加密字符
declare @i int
/*
*加密方法為原字符異或一個(gè)隨機(jī)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
--測(cè)試
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)
--輸出結(jié)果
/*
原始值: 1 2 3aA
加密后: __0'15`'17__°{1
解密后: 1 2 3aA
*/
本文來自CSDN博客，轉(zhuǎn)載請(qǐng)標(biāo)明出處：
http://blog.csdn.net/callzjy/archive/2004/05/21/20071.aspx
57、計(jì)算個(gè)人所得稅函數(shù)
-- =============================================
-- Author: Maco_wang
-- Create date: 2011-03-<Create Date,,>
-- Description: 參考htl258(Tony)的思路，改寫的計(jì)算個(gè)稅的函數(shù)
-- =============================================
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
--測(cè)試示例
select dbo.TaxRateOfPersonal(2500)
select dbo.TaxRateOfPersonal(3500)
select dbo.TaxRateOfPersonal(5000)
select dbo.TaxRateOfPersonal(9500)
--運(yùn)行結(jié)果
/*
25.0000
100.0000
250.0000
1000.0000
*/
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼