SubString扩充中文编码函数

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[SubStringN] ' )  and  xtype  in  (N ' FN ' , N ' IF ' , N ' TF ' ))
drop   function   [ dbo ] . [ SubStringN ]
GO

SET  QUOTED_IDENTIFIER  OFF
GO
SET  ANSI_NULLS  OFF
GO
/**/ /*******************************************
* function:SubStringN
* param:@str 需要分析的字符串,
* @startIndex 起始位置,
* @length 需要提取的几个字符串
* Return : 截取后返回的字符串
* Descript:本函数是针对解决字符串中包含了中文
* 中因为编码的问题不能自动截取字符串
* 的扩充函数
*
*******************************************/

create   function  SubStringN(
@str   varchar ( 4000 ), @startIndex   int , @length   int
)
returns   varchar ( 4000 )
AS
begin
-- select dbo.SubStringN('a中国人afdaf',1,2)--调用
declare   @sTmp   varchar ( 8000 ), @i   int , @itmp   int , @itmp2   int  , @stmp2   varchar ( 2 ), @len   int , @rev   varchar ( 4000 )
select   @sTmp = '' , @len = 0 , @rev = ''
select   @i = 1
while   @i <= len ( @str )
begin
select   @len = @len + 1
select   @itmp = convert ( int , substring ( convert ( varbinary , substring ( @str  , @i , 1 )), 1 , 1 ))
if ( @itmp > 127 )
select   @len = @len + 1
if ( @len >= @startIndex   and   @len < @startIndex + @length )
begin
-- select @len,@startIndex,@startIndex+@length
if ( @itmp > 127   and   @len = @startIndex )
select   @rev = @rev + ''
else   if ( @itmp > 127   and   @len > @startIndex )
select   @rev = @rev + char ( @itmp ) + char ( convert ( int , substring ( convert ( varbinary , substring ( @str

, @i , 1 )), 2 , 1 )))
else
select   @rev = @rev + char ( @itmp )
end
select   @i = @i + 1
end
return   @rev
end


GO
SET  QUOTED_IDENTIFIER  OFF
GO
SET  ANSI_NULLS  ON
GO
-- select dbo.SubStringN('a中国人afdaf',1,2)
-- select @rev
-- end