查询汉字首字母和查询是否包含中文字符

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