select dbo.f_GetPy('东莞市') as 东莞市,dbo.f_GetPy('ab中c国人') as 中国人
go
CREATE FUNCTION f_GetPy(@Str nvarchar(400))
returns nvarchar(4000)
as
begin
declare @strlen int,@re nvarchar(4000)
declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1))
insert @t select '吖','A' union all select '八','B'
union all select '嚓','C' union all select '咑','D'
union all select '妸','E' union all select '发','F'
union all select '旮','G' union all select '铪','H'
union all select '丌','J' union all select '咔','K'
union all select '垃','L' union all select '嘸','M'
union all select '拏','N' union all select '噢','O'
union all select '妑','P' union all select '七','Q'
union all select '呥','R' union all select '仨','S'
union all select '他','T' union all select '屲','W'
union all select '夕','X' union all select '丫','Y'
union all select '帀','Z'
select @strlen=len(@str),@re=''
while @strlen>0
begin
select top 1 @re=letter+@re,@strlen=@strlen-1
from @t a where chr<=substring(@str,@strlen,1)
order by chr desc
if @@rowcount=0
select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1
end
return(@re)
end
go
Create FUNCTION f_GetPy1(@Str nvarchar(max))
returns bit
as
begin
declare @IsCh bit =0
declare @strlen int,@re nvarchar(4000)
declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1))
insert @t select '吖','A' union all select '八','B'
union all select '嚓','C' union all select '咑','D'
union all select '妸','E' union all select '发','F'
union all select '旮','G' union all select '铪','H'
union all select '丌','J' union all select '咔','K'
union all select '垃','L' union all select '嘸','M'
union all select '拏','N' union all select '噢','O'
union all select '妑','P' union all select '七','Q'
union all select '呥','R' union all select '仨','S'
union all select '他','T' union all select '屲','W'
union all select '夕','X' union all select '丫','Y'
union all select '帀','Z'
select @strlen=len(@str),@re=''
while @strlen>0
begin
select top 1 @re=letter+@re,@strlen=@strlen-1
from @t a where chr<=substring(@str,@strlen,1)
order by chr desc
if @@rowcount !=0
begin
select @IsCh =1
break;
end
else
begin
select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1
end
end
return(@IsCh)
end
go
Alter FUNCTION Fn_IsChCode(@Str nvarchar(max))
Returns Bit
AS
Begin
Declare @IsCh bit =0
,@strlen int
Declare @t table
(
chr nchar(1) collate Chinese_PRC_CI_AS
)
insert @t 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 '垃' 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 '夕' 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 '&' 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 '。' 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 @strlen=len(@str)
While @strlen>0
Begin
Select Top 1 @strlen=@strlen-1
From @t a Where chr<=substring(@str,@strlen,1)
Order by chr Desc
If @@rowcount !=0
Begin
Select @IsCh =1
Break;
End
Else
Begin
Select @strlen=@strlen-1
End
End
Return(@IsCh)
End
GO