取得指定位元組長度字串

fn_GetSubstringByBytes.sql

IF EXISTS (SELECT 1 FROM sysobjects WHERE id = OBJECT_ID('[fn_GetSubstringByBytes]') AND xtype IN ('FN','IF','TF'))
   DROP FUNCTION [fn_GetSubstringByBytes];
GO

CREATE FUNCTION [fn_GetSubstringByBytes](@old_str NVARCHAR(MAX), @query_length INT)
RETURNS NVARCHAR(MAX)
BEGIN
    DECLARE @result NVARCHAR(MAX) = ''
    
    DECLARE @cur_length INT = 0;
    DECLARE @cur_index INT = 1
    WHILE(@cur_length < @query_length AND @cur_index <= LEN(@old_str))
    BEGIN
      DECLARE @letter NCHAR(1) = SUBSTRING(@old_str, @cur_index, 1)
      DECLARE @v VARBINARY(2) = CAST(@letter AS VARBINARY(2))
      DECLARE @s CHAR(4) = CONVERT(CHAR(4),@v,2)
      
      IF (SUBSTRING(@s, 3, 2) <> '00' AND @cur_length + 2 <= @query_length)
      BEGIN        
        SET @cur_length = @cur_length + 2
        SET @result = @result + @letter
      END
      ELSE
      BEGIN
        SET @cur_length = @cur_length + 1
        SET @result = @result + CAST(@letter AS CHAR(1))
      END      
            
      SET @cur_index = @cur_index + 1
    END
    
    RETURN @result
END
GO