分割字串

split string column into multiple records

電影資料表有三個字串欄位分別記錄男演員/女演員/導演, 欄位的字串是由不同資料組成, 現在想要把字串分割成單筆資料 :

DECLARE @videoId nvarchar(max) = 'M00000000007105' --電影編號

DECLARE @temp TABLE

(

  RoleType VARCHAR(Max),

  ColumnB VARCHAR(Max),

  ID INT IDENTITY(1,1)

)

-- 放置 Video 資料表的演員欄位字串

INSERT INTO @temp (RoleType,ColumnB) VALUES ('Actor',(select Actors from Video where ID=@videoId))

INSERT INTO @temp (RoleType,ColumnB) VALUES ('Actress',(select Actresses from Video where ID=@videoId))

INSERT INTO @temp (RoleType,ColumnB) VALUES ('Director',(select Directors from Video where ID=@videoId))

--select * from @temp

DECLARE @idx INT, @cnt INT

SET @idx = 1 -- 給迴圈使用的初始值

SELECT @cnt = COUNT(*) FROM @temp -- 筆數

DECLARE @SplitStr nvarchar(Max), --   暫時放置ColumnB : 演員們的姓名

        @SplitChar nvarchar(Max), -- 分割的字元

        @Columns VARCHAR(Max) --  暫時放置RoleType : 演員腳色有三種 (Actor / Actress / Director)

SET @SplitChar = ';'

DECLARE @actorInVideo table

(

    RoleType nvarchar(50),

    Name nvarchar(50)

)

-- 逐一跑每個row

WHILE @idx <= @cnt BEGIN

        SELECT @SplitStr = ColumnB,@Columns = RoleType FROM @temp WHERE id = @idx

         Declare @Count int =1  -- 巢狀迴圈的初始值

        

         -- 利用CHARINDEX函數，可抓取字串中指定字串的起始位置

         -- 利用SUBSTRING函數 ( expression , start , length ) : 可截取字符串expression，從 start 個字符開始，到Length 結束

         While (Charindex(@SplitChar,@SplitStr)>0) Begin -- 表示有分割字元存在, 這裡設定的是分號

            Insert Into @actorInVideo (RoleType,Name)

                Select @Columns,ltrim(rtrim(Substring(@SplitStr,1,Charindex(@SplitChar,@SplitStr)-1))) -- 擷取第一個分割字元前面的文字

            Set @SplitStr = Substring(@SplitStr,Charindex(@SplitChar,@SplitStr)+1,len(@SplitStr)) -- 擷取第一個分割字元後面的所有文字

            Set @Count = @Count + 1

        End

        Insert Into @actorInVideo (RoleType,Name) Select @Columns,ltrim(rtrim(@SplitStr)) -- 剩下沒有分割字元的字串

        SET @idx = @idx + 1 -- 下一個 row

end

select * from @actorInVideo