SQL 字符串截取左边指定长度字符串中文、英文

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER FUNCTION [dbo].[substring2] (@rescontent varchar(100), @nlength int,@thchar varchar(100) ) Returns varchar(255)
AS
BEGIN
  declare @rest varchar(255)
if(datalength(@rescontent)>34)
set @rest=(substring(@rescontent,0,17)+''+@thchar)
else 
set @rest=@rescontent
return @rest
END
 

select dbo.FN_MYSUBSTRING(infocontent_title,34) from t_p_infocontent
creater FUNCTION [dbo].[FN_MYSUBSTRING](
@Content nvarchar(4000),
@Size int
@suffix varchar(200)
)
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)+@suffix
--     +'@'
    --    + 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