[SQL]varchar字串從那開始擷取某個長度問題

  • 14941
  • 0
  • SQL
  • 2014-11-19

字串中除了包含英數字外,還有可能包含中文字,所以如何確實找到可以切的那個位置呢?

在論壇上有朋友討論到如何確切擷取varchar字串(字串擷取,寫入問題),原本是varchar(8)字串,要擷取成varchar(6)。

參考「在SQL語句中攫取的定長字段(characters)」的方式是先將字串轉成binary再取確定要的長度後,再轉回varchar,如下,

use  tempdb
go
CREATE TABLE T01(
    C01 [varchar](8) NULL,
    C02 [varchar](8) NULL    
)

insert INTO T01 VALUES('1/2測試','abcd測試');
insert INTO T01 VALUES('甲以丙丁','測試二動');

--欄位原本 8-->6  大小
select convert(varchar(6), substring(convert(binary, C01),1,6))
FROM T01

image

 

以上是從頭開始擷取,但如果要從中間開始擷取,要如何做呢?

一開始因為沒有注意到要擷取字串的長度,所以有可能會擷取太長的字串,而導致擷取出來的字串要串接其字串時,會被吃掉。

所以要考慮擷取的長度不能大於原字串的長度,或是改轉成varbinary,而不是binary,如下,

SELECT CAST(SUBSTRING(convert(BINARY, '1乙') , 1, 4) AS VARCHAR) + '*' AS Bin_Sub, 
CAST(SUBSTRING(convert(VARBINARY ,'1乙') , 1, 4) AS VARCHAR) + '*' AS VarBin_Sub 

image

 

因為字串中除了包含英數字外,還有可能包含中文字,所以如何確實找到可以切的那個位置呢?

如果開始切的位置剛好是中文字的中間,那就要往回推一個位置。

一開始想使用ASCII來判斷前一個字元是不是為32,是的話就表示要切的剛好是切在中文的中間,要將切的位置往前移一位,如下,

WHEN    ASCII(cast(substring(convert(varbinary, @Value),@CutStart-1,1) AS char )) = 32
    then @CutStart-1

 

但是有些中文字又無法這樣判斷。

後來又用判前後一個字判斷,也是沒辦法!

WHEN ASCII(cast(substring(convert(varbinary, @Value),@CutStart,1) AS char ))
              <> ASCII(cast(substring(convert(varbinary, @Value),@CutStart,2) AS char )) 
        then @CutStart-1

 

後來就用一個字一個字取出來計算長度,然後再跟要開始擷取的位置比較,如果有Match到就表示可以從那個位置開始切,不Match,就要將切的位置往前移一位,如下,

DECLARE @valueLen int, @idx int, @oneChar varchar(2), @incValue INT, @isMatch INT
SELECT    @valueLen = LEN(@Value), @idx = 0, @incValue = 1, @isMatch = 0
WHILE( @valueLen > @idx AND @isMatch = 0 AND @incValue < @CutStart)
BEGIN
    SELECT    @idx = @idx + 1 , @oneChar = SUBSTRING(@Value, @idx, 1), @incValue = @incValue + DATALENGTH(@oneChar)
    IF @CutStart = @incValue
        SET @isMatch = 1;
END
IF @isMatch = 0
    SET @CutStart = @CutStart - 1;

 

最後建立一個  dbo.CutString function來處理varchar字串擷取,如下,

CREATE FUNCTION dbo.CutString
    (
      @Value VARCHAR(50) ,
      @CutStart INT ,
      @CutLength INT
    )
RETURNS VARCHAR(50)
    BEGIN
        DECLARE @vLength INT
        DECLARE @result VARCHAR(64)  
        SET @Value = ISNULL(RTRIM(LTRIM(@Value)), '')  
        SET @vLength = DATALENGTH(@Value) - @CutStart + 1
        IF @CutStart > 1 
            BEGIN
                DECLARE @valueLen INT ,
                    @idx INT ,
                    @oneChar VARCHAR(2) ,
                    @incValue INT ,
                    @isMatch INT
                SELECT  @valueLen = LEN(@Value) ,
                        @idx = 0 ,
                        @incValue = 1 ,
                        @isMatch = 0
                WHILE ( @valueLen > @idx
                        AND @isMatch = 0
                        AND @incValue < @CutStart
                      ) 
                    BEGIN
                        SELECT  @idx = @idx + 1 ,
                                @oneChar = SUBSTRING(@Value, @idx, 1) ,
                                @incValue = @incValue + DATALENGTH(@oneChar)
                        IF @CutStart = @incValue 
                            SET @isMatch = 1;
                    END
                IF @isMatch = 0 
                    SET @CutStart = @CutStart - 1;
            END
    
 
      
        IF @vLength > @CutLength 
            BEGIN
                SET @vLength = @CutLength
            END

        SELECT  @result = CASE WHEN @Value = '' THEN ''
                               ELSE CONVERT(VARCHAR(64), SUBSTRING(CONVERT(VARBINARY(MAX), @Value),
                                                              @CutStart,
                                                              @vLength))
                          END
        RETURN @result
    END;
go

SELECT  '--' + dbo.CutString('甲乙丙丁1開始了   ', 1, 8) + '--' 'A' ,
        '--' + dbo.CutString('甲乙丙丁1開始了   ', 9, 20) + '--' 'B' ,
        '--' + dbo.CutString('甲乙丙丁', 9, 20) + '--' 'BX' ,
        '--' + dbo.CutString('甲乙1丙丁開始了   ', 1, 8) + '--' 'C' ,
        '--' + dbo.CutString('甲乙1丙丁開始了   ', 9, 20) + '--' 'D' ,
        '--' + dbo.CutString('', 1, 8) + '--' 'E' ,
        '--' + dbo.CutString('甲乙丙丁開始了   ', 9, 20) + '--' 'F' ,
        '--' + dbo.CutString('甲乙丙丁開始了   ', 7, 20) + '--' 'g'

image

 

在此分享給大家,如果有更好的方式也請讓我知道,謝謝大家。

2014/11/19 修改varbinary => varbinary(max)

新增大字串的 dbo.CutStringMAX function

CREATE FUNCTION dbo.CutStringMAX
    (
      @Value VARCHAR(max) ,
      @CutStart INT ,
      @CutLength INT
    )
RETURNS VARCHAR(max)
    BEGIN
        DECLARE @vLength INT
        DECLARE @result VARCHAR(max)  
        SET @Value = ISNULL(RTRIM(LTRIM(@Value)), '')  
        SET @vLength = DATALENGTH(@Value) - @CutStart + 1
        IF @CutStart > 1 
            BEGIN
                DECLARE @valueLen INT ,
                    @idx INT ,
                    @oneChar VARCHAR(2) ,
                    @incValue INT ,
                    @isMatch INT
                SELECT  @valueLen = LEN(@Value) ,
                        @idx = 0 ,
                        @incValue = 1 ,
                        @isMatch = 0
                WHILE ( @valueLen > @idx
                        AND @isMatch = 0
                        AND @incValue < @CutStart
                      ) 
                    BEGIN
                        SELECT  @idx = @idx + 1 ,
                                @oneChar = SUBSTRING(@Value, @idx, 1) ,
                                @incValue = @incValue + DATALENGTH(@oneChar)
                        IF @CutStart = @incValue 
                            SET @isMatch = 1;
                    END
                IF @isMatch = 0 
                    SET @CutStart = @CutStart - 1;
            END
    
 
      
        IF @vLength > @CutLength 
            BEGIN
                SET @vLength = @CutLength
            END

        SELECT  @result = CASE WHEN @Value = '' THEN ''
                               ELSE CONVERT(VARCHAR(max), SUBSTRING(CONVERT(VARBINARY(MAX), @Value),
                                                              @CutStart,
                                                              @vLength))
                          END
        RETURN @result
    END;
go

 

Hi, 

亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活

請大家繼續支持 ^_^