首页 > 代码库 > SQL常用自定义函数
SQL常用自定义函数
去除字符串中连续的分割符
--创建函数
create function [dbo].[m_delrepeatsplit]
(
@str varchar(2000),
@split nvarchar(200)
)
returns nvarchar(2000)
as
begin
--begin declare
declare @count int,@i int,@isnull int
declare @newchar nvarchar(200),@nn nvarchar(300)
set @count=len(@str);set @i=1;set @isnull=1;set @nn=‘‘;
--end declare
--begin while
while @i<@count+1
begin
set @newchar=substring(@str,@i,1)
if(@isnull=1)
begin
set @nn=@nn+@newchar;
if(@newchar=@split)
begin
set @isnull=0;
end
else
begin
set @isnull=1;
end
end
else
begin
if(@newchar=@split)
begin
set @isnull=0;
end
else
begin
set @nn=@nn+@newchar;
set @isnull=1;
end
end
set @i=@i+1;
end
--end while
return @nn
end
--2、测试示例
declare @str nvarchar(200)
set @str=‘1 2 3 4 555 6 7 7‘;
declare @split nvarchar(200)
set @split=‘ ‘;
select dbo.m_delrepeatsplit(@str,@split) as newchar
--3、运行结果
/*
newchar
------------------
1 2 3 4 555 6 7 7
*/
求第一个字符串中第二个串的个数
--创建函数
create function [dbo].[m_count]
(
@str_one nvarchar(200), --第一个字符串
@str_two nvarchar(200) --第二个字符串
)
returns int as
begin
declare @sqlcount int
select @sqlcount=(len(@str_one)-len(replace(@str_one,@str_two,‘‘)))/len(@str_two)
return @sqlcount
end
--测试示例
select dbo.m_count(‘sqlserver‘,‘e‘) as [count]
--运行结果
/*
count
-----------
2
*/
综合模糊查询
--创建函数
create function [dbo].[m_fuzzyquery_v1]
(
@str nvarchar(2000)
)
returns nvarchar(2000)
as
begin
declare @count int,@i int;
declare @newchar nvarchar(200),@nn nvarchar(300),@hh nvarchar(200)
set @count=len(@str);set @i=1;set @nn=‘‘;
while @i<@count+1
begin
set @newchar=substring(@str,@i,1)+‘%‘
set @nn=@nn+@newchar;
set @i=@i+1;
end
set @hh=‘%‘+@nn
return @hh
end
--测试数据
declare @table table (connect varchar(30))
insert into @table
select ‘我爱程序‘ union all
select ‘我的程序生活‘ union all
select ‘绝对无聊的生活‘ union all
select ‘活得好累‘ union all
select ‘程序员的生活‘ union all
select ‘序论‘ union all
select ‘生机‘ union all
select ‘生活杂志‘ union all
select ‘我只是随便写写‘ union all
select ‘真的是要来搜索的‘ union all
select ‘程序员一生的活路‘
--普通的模糊查询
select * from @table where connect like ‘%程序生活%‘
--运行结果
/*
connect
------------------------------
我的程序生活
*/
--应用函数查询
select * from @table where connect like ( select dbo.[m_fuzzyquery_v1](‘程序生活‘))
--运行结果
/*
connect
------------------------------
我的程序生活
程序员的生活
程序员一生的活路
*/
取出字符串中的汉字、字母或是数字
go
--创建函数(得到字符串中的汉字)
create function [dbo].[m_getchinese]
(
@chinese nvarchar(max)
)
returns varchar(100)
as
begin
while patindex(‘%[^吖-咗]%‘,@chinese) > 0
begin
set @chinese = stuff(@chinese,patindex(‘%[^吖-咗]%‘,@chinese),1,N‘‘);
end
return @chinese
end
go
--创建函数(得到字符串中的字母)
create function [dbo].[m_getstr](@maco varchar(100))
returns varchar(max)
as
begin
while patindex(‘%[^a-z]%‘,@maco) > 0
begin
set @maco=stuff(@maco,patindex(‘%[^a-z]%‘,@maco),1,‘‘)
end
return @maco
end
go
--创建函数(得到字符串中的数字)
create function [dbo].[m_getnumber]
(
@mysql_one nvarchar(200)
)
returns varchar(200)
begin
declare @mysql_two varchar(200)
select @mysql_two=
substring(@mysql_one,patindex(‘%[0-9.]%‘,@mysql_one),patindex(‘%[^0-9.]%‘,substring(@mysql_one,patindex(‘%[0-9.]%‘,@mysql_one),
len(@mysql_one)-patindex(‘%[0-9.]%‘,@mysql_one)+1))-1)
return @mysql_two;
end
--测试
select dbo.[m_getchinese](‘China2009中国HRB4-1v‘)
select dbo.[m_getstr](‘China2009中国HRB4-1v‘)
select dbo.[m_getnumber](‘China2009中国HRB4-1v‘)
--运行结果
/*
-----------
中国
-----------
ChinaHRBv
-----------
2009
*/
--说明一下
--上面这个取数字是可以取浮点型的
select dbo.[m_getnumber] (‘字段.456A(AA)A‘)--正常
select dbo.[m_getnumber] (‘CHinese2.1day‘)--正常
select dbo.[m_getnumber] (‘Name5.01From‘)--正常
select dbo.[m_getnumber] (‘9898Address‘)--正常
select dbo.[m_getnumber] (‘aaaaaForm2.3333‘)--错误
--修正函数
go
/* 取出字符串中间的数字(第二版)*/
create function [dbo].[m_getnumberV2.0]
(
@mysql_one nvarchar(200)
)
returns varchar(200)
begin
declare @mysql_two varchar(200)
declare @sql_one int
declare @sql_two int
select @sql_one= patindex(‘%[0-9.]%‘,@mysql_one)
select @sql_two=
patindex(‘%[^0-9.]%‘,
substring(@mysql_one,patindex(‘%[0-9.]%‘,@mysql_one),len(@mysql_one)-patindex(‘%[0-9.]%‘,@mysql_one)+1))
if @sql_two=0
begin
select @mysql_two= substring (@mysql_one,@sql_one,len(@mysql_one)+1-@sql_one)
end
else
begin
select @mysql_two=substring (@mysql_one,@sql_one,@sql_two-1)
end
return @mysql_two;
end
--测试示例
select dbo.[m_getnumberV2.0] (‘字段.456A(AA)A‘)--正常
select dbo.[m_getnumberV2.0] (‘CHinese2.1day‘)--正常
select dbo.[m_getnumberV2.0] (‘Name5.01From‘)--正常
select dbo.[m_getnumberV2.0] (‘9898Address‘)--正常
select dbo.[m_getnumberV2.0] (‘aaaaaForm2.3333‘)--正常
根据字符分割字符串的三种写法
go
--创建函数(第一版)
create function [dbo].[m_split](@c varchar(2000),@split varchar(2))
returns @t table(col varchar(200))
as
begin
while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),‘‘)
end
insert @t(col) values (@c)
return
end
--下面两种是在论坛看到高手们发的
go
--创建函数(第二版)(fredrickhu(小F))
create function [dbo].[f_split](@s varchar(8000), @split varchar(10) )
returns table
as
return
(select substring(@s,number,charindex(@split,@s+@split,number)-number)as col
from master..spt_values
where type=‘p‘ and number<=len(@s+‘a‘)
and charindex(@split,@split+@s,number)=number)
go
--创建函数(第三版)(dawugui(爱新觉罗.毓华))
create function [dbo].[d_split] (@inputstr varchar(8000),@seprator varchar(10))
returns @temp table (a varchar(200))
as
begin
declare @i int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)
while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))
set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end
if @inputstr <> ‘/‘
insert @temp values(@inputstr)
return
end
--测试示例
declare @sql varchar(20)
set @sql=‘A,B,C,D,E‘
select * from dbo.m_split(@sql,‘,‘)
select * from dbo.f_split(@sql,‘,‘)
select * from dbo.d_split(@sql,‘,‘)
--运行结果(结果是相同的)
/*
col
---------
A
B
C
D
E
*/
根据身份证得到生日函数
go
--创建函数(函数来自csdn，作者不详)
create function [dbo].[Get_birthday]
(
@idcardno nvarchar(50)
)
returns varchar(10)
as
begin
declare @birthday datetime
if (len(@idcardno)=15 or len(@idcardno)=16) and substring(@idcardno,9,2) between 1 and 12 andsubstring(@idcardno,11,2) between 1 and 31
set @birthday= convert(varchar(10),‘19‘+substring(@idcardno,7,2)+‘-‘+substring(@idcardno,9,2)+‘-‘+substring(@idcardno,11,2),120)
else if len(@idcardno)=18 and substring(@idcardno,7,2)>=19 and substring(@idcardno,11,2) between 1 and 12 andsubstring(@idcardno,13,2) between 1 and 31
set @birthday= convert(varchar(10),substring(@idcardno,7,4)+‘-‘+substring(@idcardno,11,2)+‘-‘+substring(@idcardno,13,2),120)
else
set @birthday=null
return(convert(varchar(10),@birthday,120))
end
--测试示例
select dbo.[Get_birthday](‘222222198306043213‘)
--运行结果
/*
1983-06-04
*/
根据身份证计算性别函数
Go
--创建函数(函数来自csdn，作者不详)
create function [dbo].[Get_sex]
(
@idcardno nvarchar(50)
)
returns int
as
begin
declare @sex int
if (len(@idcardno)=18 and isnumeric(substring(@idcardno,17,1))=1 )
set @sex= (case when substring(@idcardno,17,1) in(1,3,5,7,9) then 1
when substring(@idcardno,17,1) in(2,4,6,7,0) then 2 else 0 end)
else if (len(@idcardno)=15 and isnumeric(substring(@idcardno,15,1))=1 )
set @sex= (case when substring(@idcardno,15,1) in(1,3,5,7,9) then 1
when substring(@idcardno,15,1) in(2,4,6,7,0) then 2 else 0 end)
else
set @sex=0
return(@sex)
end
--测试示例
select dbo.[Get_sex](‘222222198306043213‘)
--运行结果(1表示男0表示女)
/*
1
*/
将身份证的15位号码升级为18位
go
--创建函数(此函数来自于csdn，作者不详)
create function [dbo].[id15to18] (@id15 char(15))
returns char(18)
as
begin
declare @id18 char(18)
declare @s1 as integer
declare @s2 as integer
declare @s3 as integer
declare @s4 as integer
declare @s5 as integer
declare @s6 as integer
declare @s7 as integer
declare @s8 as integer
declare @s9 as integer
declare @s10 as integer
declare @s11 as integer
declare @s12 as integer
declare @s13 as integer
declare @s14 as integer
declare @s15 as integer
declare @s16 as integer
declare @s17 as integer
declare @s18 as integer
set @s1=substring(@id15,1,1)
set @s2=substring(@id15,2,1)
set @s3=substring(@id15,3,1)
set @s4=substring(@id15,4,1)
set @s5=substring(@id15,5,1)
set @s6=substring(@id15,6,1)
set @s7=1
set @s8=9
set @s9=substring(@id15,7,1)
set @s10=substring(@id15,8,1)
set @s11=substring(@id15,9,1)
set @s12=substring(@id15,10,1)
set @s13=substring(@id15,11,1)
set @s14=substring(@id15,12,1)
set @s15=substring(@id15,13,1)
set @s16=substring(@id15,14,1)
set @s17=substring(@id15,15,1)
set @s18=((@s1*7)+(@s2*9)+(@s3*10)+(@s4*5)+(@s5*8)+(@s6*4)+(@s7*2)+(@s8*1)
+(@s9*6)+(@s10*3)+(@s11*7)+(@s12*9)+(@s13*10)+(@s14*5)+(@s15*8)+(@s16
*4)+(@s17*2))%11
set @id18=substring(@id15,1,6)+‘19‘+substring(@id15,7,9)
+case when @s18=0 then ‘1‘
when @s18=1 then ‘0‘
when @s18=2 then ‘x‘
when @s18=3 then ‘9‘
when @s18=4 then ‘8‘
when @s18=5 then ‘7‘
when @s18=6 then ‘6‘
when @s18=7 then ‘5‘
when @s18=8 then ‘4‘
when @s18=9 then ‘3‘
when @s18=10 then ‘2‘
end
return @id18
end
--测试示例
select [dbo].[id15to18](‘222222830604321‘)
--运行结果
/*
222222198306043213
*/
通过身份证获得户籍
go
--创建函数(此函数来自于csdn，作者不详)
create function [dbo].[f_getcityfromcid] (@cid varchar(18))
returns varchar(50)
as
begin
declare @acity varchar(1000)
set @acity = ‘____,____,____,____,____,____,____,____,____,____,____,北京__,天津__,河北__,山西__,内蒙古_,____,____,____,____,____,辽宁__,吉林__,黑龙江_,____,____,____,____,____,____,____,上海__,江苏__,浙江__,安微__,福建__,江西__,山东__,____,____,____,河南__,湖北__,湖南__,广东__,广西__,海南__,____,____,____,重庆__,四川__,贵州__,云南__,西藏__,____,____,____,____,____,____,陕西__,甘肃__,青海__,宁夏__,新疆__,____,____,____,____,____,台湾__,____,____,____,____,____,____,____,____,____,香港__,澳门__,____,____,____,____,____,____,____,____,国外__,‘
select @acity=replace(@acity,‘ ‘,‘‘);
set @cid = upper(@cid)
if (len(@cid) <> 18 or patindex(‘%[^0-9x]%‘,@cid) > 0)
return ‘这不是合法的身份证‘
if substring(@acity,cast(left(@cid,2) as int)* 5+1,4) = ‘‘
return ‘这身份证的地区码不存在‘
return ‘您的户籍是：‘+replace(substring(@acity,cast(left(@cid,2) as int)* 5+1,4),‘_‘,‘‘)
end
--测试示例
select dbo.[f_getcityfromcid](‘222222198306043213‘)
--运行结果
/*
您的户籍是：吉林
*/
多个数据项的字符串取指定位置字符
-- Author: happyflsytone
-- Date:2008-11-05 14:59:34
-- 创建函数
create function [dbo].[split_str]
(
@s varchar(8000), --包含多个数据项的字符串
@index int, --要获取的数据项的位置
@split varchar(10) --数据分隔符
)
returns varchar(100)
as
begin
if @s is null return(null)
begin
declare @splitlen int
select @splitlen=len(@split+‘A‘)-2
end
while @index>1 and charindex(@split,@s+@split)>0
begin
select @index=@index-1,@s=stuff(@s,1,charindex(@split,@s+@split)+@splitlen,‘‘)
end
return(isnull(left(@s,charindex(@split,@s+@split)-1),‘‘))
end
--测试示例
select dbo.split_str(‘1-2-3-4‘,3,‘-‘)
--运行结果
/*
3
*/
人民币小写金额转大写
--1、人民币小写金额转大写(第一版)
====================================================================
-- Title: 人民币小写金额转大写
-- Author: dobear Mail(MSN): dobear_0922@hotmail.com
-- Environment: Vista + SQL2005
-- Date: 2008-06-12
-- Remark: dobear原创，转载请注明出处，有问题请发Mail告之
-- ====================================================================
go
--创建函数
create function [dbo].[fn_getformatmoney] (@money numeric(14,2))
returns nvarchar(32) as
begin
declare @money_num nvarchar(20) --存储金额的字符形式
, @money_chn nvarchar(32) --存储金额的中文大写形式
, @n_chn nvarchar(1), @i int --临时变量
select @money_chn=case when @money>=0 then ‘‘ else ‘(负)‘ end
, @money=abs(@money)
, @money_num=stuff(str(@money, 15, 2), 13, 1, ‘‘) --加前置空格补齐到位（去掉小数点）
, @i=patindex(‘%[1-9]%‘, @money_num) --找到金额最高位
while @i>=1 and @i<=14
begin
set @n_chn=substring(@money_num, @i, 1)
if @n_chn<>‘0‘ or (substring(@money_num,@i+1,1)<>‘0‘ and @i not in(4, 8, 12, 14)) --转换阿拉伯数字为中文大写形式
set @money_chn=@money_chn+substring(‘零壹贰叁肆伍陆柒捌玖‘, @n_chn+1, 1)
if @n_chn<>‘0‘ or @i in(4, 8, 12) --添加中文单位
set @money_chn=@money_chn+substring(‘仟佰拾亿仟佰拾万仟佰拾圆角分‘,@i,1)
set @i=@i+1
end
set @money_chn=replace(@money_chn, ‘亿万‘, ‘亿‘) --当金额为x亿零万时去掉万
if @money=0 set @money_chn=‘零圆整‘ --当金额为零时返回‘零圆整‘
if @n_chn=‘0‘ set @money_chn=@money_chn+‘整‘ --当金额末尾为零分时以‘整‘结尾
return @money_chn --返回大写金额
end
go
--测试示例
select dbo.fn_getformatmoney(4545.44)
--运行结果
/*
肆仟伍佰肆拾伍圆肆角肆分
*/
--2、人民币小写金额转大写(第二版)
/*编写者：(博客天地www.inbaidu.com )
创建时间：
功能：小写金额转换成大写
参数：@LowerMoney 小写金额加上小数点最长可以保留位
输出：大写金额
简介：SQL版小写金额转换成大写金额（最多可以精确到小数点四位）
注： Decimal 数据类型最多可存储个数字
转载：请保留以上信息,谢谢！！！
********************************************************/
go
--创建函数
create function [dbo].[lowertoupper](@lowermoney decimal(38,4))
returns varchar(200) --返回的大写金额的字符
as
begin
declare @lowerstr varchar(50) --小写金额
declare @upperstr varchar(200) --大写金额
declare @uppertmp varchar(15) --大写金额的临时字符串
declare @i int --递增量
declare @lowerlen int --小写金额的总长度
set @lowerstr = @lowermoney --把decimal型的值全部赋给字符串变量注:(赋值过去的话如在字符串变量中是显示.0000 因为小数位精确到四位,没有的话，它会自动补)
set @lowerstr = replace(@lowerstr,‘.‘,‘‘) --把小数点替换成空字符 --精确到小数点的四位角分厘毫
set @lowerlen = len(@lowerstr) --获取小写金额的总长度(包括四个小数位)
select @i = 1,@upperstr = ‘‘,@uppertmp = ‘‘ --设置默认初始值
while @i <= @lowerlen
begin
set @uppertmp = case
when substring(@lowerstr,@lowerlen - @i + 1,1) = ‘0‘ and @i = 5 and(convert(int,right(@lowerstr,4)) = 0 or @lowerlen > 5) then ‘元‘ --注：如果个位为的话,并且四位小数都是或者它的长度超过(也就是超过元)，则为元
--when substring(@lowerstr,@lowerlen - @i + 1,1) = ‘0‘ then ‘‘
else
+ case substring(@lowerstr,@lowerlen - @i + 1,1) --看当前位是数字几,就直接替换成汉字繁体大写
when ‘0‘ then ‘零‘
when ‘1‘ then ‘壹‘
when ‘2‘ then ‘贰‘
when ‘3‘ then ‘叁‘
when ‘4‘ then ‘肆‘
when ‘5‘ then ‘伍‘
when ‘6‘ then ‘陆‘
when ‘7‘ then ‘柒‘
when ‘8‘ then ‘捌‘
when ‘9‘ then ‘玖‘
end
+ case @i
when 1 then ‘毫‘
when 2 then ‘厘‘
when 3 then ‘分‘
when 4 then ‘角‘
when 5 then ‘元‘
when 9 then ‘萬‘
when 13 then ‘亿‘
when 17 then ‘兆‘
when 21 then ‘京‘
when 25 then ‘垓‘
when 29 then ‘杼‘
when 33 then ‘穰‘
when 37 then ‘沟‘ --decimal型最大长度是后面的就不用再考虑了
else
+ case @i%4
when 2 then ‘拾‘ --拾10 14 18 22 26 30 34 38 …………
when 3 then ‘佰‘ --佰11 15 19 23 27 31 35 39 …………
when 0 then ‘仟‘ --仟12 16 20 24 28 32 36 40 …………
end
end
end
set @upperstr = isnull(@uppertmp,‘‘) + isnull(@upperstr,‘‘)
set @i = @i + 1
end
if convert(int,right(@lowerstr,4)) = 0 set @upperstr = left(@upperstr,len(@upperstr)-8) + ‘整‘ --判断小数位数是不是都是,是就可以取整
while patindex(‘%零[仟佰拾角分厘毫零]%‘,@upperstr) <> 0 --把零拾或零佰或零零变成一个零
begin
set @upperstr = stuff(@upperstr,patindex(‘%零[仟佰拾角分厘毫零]%‘,@upperstr),2,‘零‘)
end
while patindex(‘%[沟穰杼垓京兆亿萬]零[沟穰杼垓京兆亿萬]%‘,@upperstr) <> 0 --把零萬或零亿的清空掉
begin
select @upperstr = stuff(@upperstr,patindex(‘%[沟穰杼垓京兆亿萬]零[沟穰杼垓京兆亿萬]%‘,@upperstr)+1,2,‘‘)
end
while patindex(‘%[仟佰拾]零[沟穰杼垓京兆亿萬]%‘,@upperstr) <> 0 --把类似拾零萬或佰零萬或仟零萬中间的零清空掉
begin
select @upperstr = stuff(@upperstr,patindex(‘%[仟佰拾]零[沟穰杼垓京兆亿萬]%‘,@upperstr)+1,1,‘‘)
end
if patindex(‘%_零[元]%‘,@upperstr) <> 0 --把类似拾零元或百零元中间的零清空掉
begin
select @upperstr = stuff(@upperstr,patindex(‘%_零[元]%‘,@upperstr) + 1,1,‘‘)
end
else if (patindex(‘零[元]%‘,@upperstr) <> 0) and (convert(int,right(@lowerstr,4)) <> 0) --判断当前否是零元开头，并且后面的四个小数不为
begin
select @upperstr = stuff(@upperstr,patindex(‘零[元]%‘,@upperstr),2,‘‘) --把零元清空掉
end
if right(@upperstr,1) = ‘零‘ set @upperstr = left(@upperstr,len(@upperstr)-1) --如果最后一位是零也清空掉
if @upperstr = ‘元整‘ set @upperstr = ‘零‘ + @upperstr --如果只是的话，就显示零元整
return @upperstr --返回大写金额
end
--测试示例
Select dbo.LowerToUpper(120000000) --壹亿贰仟萬元整
Select dbo.LowerToUpper(102000000) --壹亿零贰佰萬元整
Select dbo.LowerToUpper(100200000) --壹亿零贰拾萬元整
Select dbo.LowerToUpper(100020000) --壹亿零贰萬元整
Select dbo.LowerToUpper(100002000) --壹亿贰仟元整
--运行结果
/*
壹亿贰仟萬元整
壹亿零贰佰萬元整
壹亿零贰拾萬元整
壹亿零贰萬元整
壹亿贰仟元整
*/
检查给定串是否存在于由区间及点集的结合内
------------------------------------
-- Author: happyflystone
-- Date:2009-07-20
-- Parameter: @CardString
-- 被查询的串，形如：-13300001234,13300002230,13300002300
-- @CardNo 要查询的串
-- Return : int 0 -- 不存在于搜索串的范围内
-- 1 -- 存在于
-- 转载请注明出处。更多请访问：http://blog.csdn.net/happyflystone
-- 原帖地址：http://blog.csdn.net/happyflystone/archive/2009/07/21/4365264.aspx
------------------------------------
--创建函数
Create function IsInCardString(@CardString varchar(8000),@CardNo varchar(11))
returns int
as
begin
declare @temp table(a varchar(200))
declare @i int
set @CardString = rtrim(ltrim(@CardString))+‘,‘
set @i = charindex(‘,‘, @CardString)
while @i >= 1
begin
insert @temp values(left(@CardString, @i - 1))
set @CardString = substring(@CardString, @i + 1, len(@CardString) - @i)
set @i = charindex(‘,‘, @CardString)
end
if exists(select 1
from (
select case when charindex(‘-‘,a) > 0 then left(a,11) else a end as s,
case when charindex(‘-‘,a) > 0 then right(a,11) else a end as e
from @temp
) a
where @CardNo between s and e)
set @i= 1
else
set @i= 0
return @i
end
go
--测试示例
declare @CardString varchar(1000)
set @CardString =‘13300000000-13300001234,13300002230,13300002300,13300002302,13300004101-13300004204,13300004212,13300004310‘
declare @CardNo varchar(1000)
set @CardNo = ‘13300000001‘ --存在
select dbo.IsInCardString(@CardString,@CardNo) as result1
set @CardNo = ‘13300001235‘ --不存在
select dbo.IsInCardString(@CardString,@CardNo) as result2
--运行结果
/*
result1
-----------
1
01、去除字符串中的html标记及标记中的内容
02、去除字符串中连续的分割符
03、求第一个字符串中第二个串的个数
04、综合模糊查询
05、将十进制转成十六进制
06、求两个字符串中相同的汉字及字母的个数
07、生成n位随机字符串
08、取出字符串中的汉字、字母或是数字
09、根据字符分割字符串的三种写法
10、将数字转换千分位分隔形式
11、取汉字首字母的两个函数
12、根据身份证得到生日函数
13、根据身份证计算性别函数
14、将身份证的15位号码升级为18位
15、通过身份证获得户籍
16、多个数据项的字符串取指定位置字符
17、中缀算术转后缀算术表达式并计算的函数
18、人民币小写金额转大写
19、向左填充指定字符串
20、将整型数字转换为大写汉字
21、检查给定串是否存在于由区间及点集的结合内
22、根据日期返回星座
23、计算两个日期之间的工作日
24、根据年月生成日历函数
25、从第一个汉字开始分割字符串
26、过滤掉字符串中重复的字符
27、根据日期得到星期的函数
28、根据年度判断是否是闰年
29、完善SQL农历转换函数
30、SQL简繁转换函数
31、自定义函数实现位操作
32、求某段时间内星期几的天数
33、根据进舍位或四舍五入来求值
34、字符串转成16进制函数
35、去掉字段中多个带区号电话号码前面的区号
36、SQL2000/2005字符串拆分为列表通用函数
37、求字符串中汉字的个数
38、得到条形码的校验位函数
39、根据年月得到当月的天数
40、将一个正整数分解为m个2的n次方的和
41、SQL位移运算函数
42、得到汉字笔画函数
43、SQL数字转英文函数
44、全角半角转换函数
45、返回两个时间范围内的一个随机时间
46、获取元素个数的函数
47、获取指定索引的值的函数
48、根据年得到所有星期日的日期
49、生成两个时间之间的所有日期
50、无序字符比较函数
51、在SQL SERVER中实现RSA加解密函数（第一版）
52、在SQL SERVER中实现RSA加解密函数（第二版）
53、输出指定格式的数据列
54、汉字转拼音函数
55、数字转IP地址函数
56、对字符串进行加密解密
57、计算个人所得税函数
result2
-----------
0
*/
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‘, ‘,‘)
--运行结果
/*
1
0
*/
SQL常用自定义函数