Example:

select len('你好a'),
substring('你好a',1,1),ascii('你')

结果是

                             

-----------       ---- -       ----
3           你          196

create function fn_TruncSemiHanzi
(@str varchar(4000))

returns  varchar(8000)

as

begin

declare

@sTmp varchar(8000),@i int,
@itmp int,@itmp2 int ,@stmp2 varchar(2) 

select @sTmp='

  select @i=1

  while @i<=len(@str)

  begin

     select @itmp=convert(int,substring
(convert(varbinary,substring(@str ,@i,1)),1,1))
 --截取一个字节

    if @itmp>127  

    begin

      --大于127检查后面一个字节

select @itmp2=convert(int,substring
(convert(varbinary,substring(@str ,@i,1)),2,1))

       if @itmp2>127 

      begin

        select @stmp2=char(@itmp)+char(@itmp2)  
 --是一个完整的汉字 

     end 

     else

     begin

        select @stmp2=char(@itmp2)   --丢弃半个汉字

    end

  end

  else

  begin

      select @sTmp2=char(@itmp)

  end 

   select @sTmp=@sTmp+@stmp2 

   select @i=@i+1

  end

   return @stmp

end