sql 2005 中英文混合的字符串截取函数

　　做网站的时候经常会碰到要截取字符串的情况。但Len函数不管是中文字符，还是英文字符，统统按一个单位来计算，由于一个中文字符的宽度通常是两个英文字符宽度，在中英文混合的情况下字符串实际占用的宽度就不好计算了，如果按照Len函数计算的长度来截取字符串截出来的效果就会长短不一，下面是按照一个汉字相当于两个英文字符来计算字符串长度截取字符串的数据库函数代码

CREATE FUNCTION [dbo].[FNC__CUT_STRING_BY_WIDTH_EN_CN](
@Content nvarchar(4000),
@Size int
)
RETURNS nvarchar(4000)
AS
BEGIN
--   SET @Content = N'Office of Commissioner Insurance - Establishment of an Independent Insurance Authority in Hong Kong (2009)'
--   SET @Size = 26
SET @Content = LTRIM(RTRIM(REPLACE( REPLACE(@Content,CHAR(10),' '),CHAR(13),' ')))
if @Size > 0 AND @Size < 2 * LEN (@Content)
begin
   declare @Pos INT --Current pointer
   DECLARE @sPos INT --Current stay pointer
   DECLARE @PrePos INT --Previous stay pointer
   declare @tempS nvarchar(4)
   DECLARE @tempNextS nvarchar(4)
   declare @tempSize INT
   DECLARE @unicodeS INT
   DECLARE @unicodeNextS INT
   set @Pos = 1
   set @sPos = 0
   SET @prePos = 0
   SET @tempS = N''
   SET @tempNextS = N''
   SET @tempSize = 0

   WHILE @Pos < len(@Content)
   BEGIN
    IF @tempSize < @Size
    BEGIN
     SET @tempS = SUBSTRING(@Content,@Pos,1)
     SET @tempNextS = SUBSTRING(@Content,@Pos+ 1,1)
     SET @unicodeS = UNICODE(@tempS)
     SET @unicodeNextS = UNICODE(@tempNextS)

     IF @unicodeS > 254
     BEGIN
      SET @tempSize = @tempSize + 2
      SET @sPos = @Pos
     END
     ELSE
     BEGIN
      SET @tempSize = @tempSize + 1

      -- Do not turncate the word just length as @Size by add symbol before and after the word
      IF @unicodeS < 48 OR (@unicodeS > 57 AND @unicodeS < 65) OR (@unicodeS > 90 AND @unicodeS < 97) OR @unicodeS > 122
       OR @unicodeNextS < 48 OR (@unicodeNextS > 57 AND @unicodeNextS < 65) OR (@unicodeNextS > 90 AND @unicodeNextS < 97) OR @unicodeNextS > 122
      BEGIN
       SET @sPos = @Pos
      END

     END
    END

    IF @tempSize > @Size - 2 AND @unicodeNextS > 254 -- if next letter is Chinese then turncate the string
    BEGIN
     SET @tempSize = @Size
     SET @sPos = @Pos
    END
    ELSE IF @tempSize = @Size AND @PrePos = @sPos -- else if the English word length is large than the @Size then turncate the word
    BEGIN
     SET @sPos = @Pos
    END

    IF @tempSize >= @Size
    BEGIN
     set @Content = left(@Content, @sPos)
--     +'@'
        + nchar(13)
      + right(@Content, len(@Content) - @sPos)
     SET @tempSize = 0
     SET @PrePos = @sPos
     set @Pos = @sPos + 1
    END
    set @Pos = @Pos + 1
    SET @unicodeS = 0
    SET @unicodeNextS = 0
   END

END

RETURN @Content
END