MSSQL中取字符中的汉字或双字节字符

1.SQL中截取中文字符("吖-咗"这个区间几乎能包括所有的汉字)

-- A.
create   function  get_china( @s   varchar ( 50 ))
returns   varchar ( 50 )
as
begin
declare   @i   int
set   @i = 1
while   patindex ( ' %[^吖-咗]% ' , @s )  >   0   and   @i <= len ( @s )
begin
if   substring ( @s , @i , 1 )  not   like   ' %[吖-咗]% '
set   @s = stuff ( @s , @i , 1 , '' )
else
set   @i = @i + 1
end
return   @s
end

select  dbo.get_china( ' 但是111中国aaa人test我***是啊kao多多うェ ' )
drop   function  dbo.get_china


-- B.
create   function  getnewstr
( @oldstr   varchar ( 100 ))
returns   varchar ( 100 )
as
begin
declare   @i   int
set   @i   =   1
while   @i   <=   len ( @oldstr )
if   substring ( @oldstr ,  @i ,  1 )  like ( ' [^吖-座] ' )
set   @oldstr   =   replace ( @oldstr ,  substring ( @oldstr ,  @i ,  1 ),  '' )
else
set   @i   =   @i   + 1
return   @oldstr
end
go

select  result  =  dbo.getnewstr( ' 但是111中国aaa人test我***是啊kao多多うェ ' )
drop   function  dbo.getnewstr

2.字符中无全角,但是还包含双字节字符,比如汉字,日文,汉文

-- A.
declare   @s   varchar ( 100 ), @i   int , @s2   varchar ( 100 )
set   @s = ' 但是111中国aaa人test我***是啊kao多多うェ '
set   @i = 1
while    @i <= len ( @s )
begin
     if   ascII ( substring ( @s , @i , 1 ))  between   133    and   223
        set   @i = @i + 1  
     else
     begin
        set   @s   =   stuff ( @s , @i , 1 , '' )
        set   @i   =   1
     end
end
select   @s

-- B.
DECLARE   @s   VARCHAR ( 100 ), @s1   VARCHAR ( 100 )
SELECT   @s = ' 但是111中国aaa人test我***是啊kao多多うェ ' , @s1 = ''
WHILE   LEN ( @s ) > 0
     SELECT   @s1 = CASE   WHEN   DATALENGTH ( RIGHT ( @s , 1 )) = 2   THEN   RIGHT ( @s , 1 )  ELSE   ''   END   +   @s1 , @s =LEFT ( @s , LEN ( @s ) - 1 )
SELECT   @s1

--来自优快云

转载于:https://www.cnblogs.com/fxllx82/archive/2008/04/07/1140577.html