亂馬客

在論壇上有朋友討論到如何確切擷取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

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

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

所以要考慮擷取的長度不能大於原字串的長度，或是改轉成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 

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

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

一開始想使用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'

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

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:從零開始的軟體開發生活」

請大家繼續支持 ^_^