Nội dung :

1. STRING_SPLIT
2. CROSS APPLY
3. Ví dụ nâng cao ứng dụng STRING_SPLIT và CROSS APPLY

• STRING_SPLIT(@String, điều kiện tách): Sql server sử dụng function này với mục đích tách 1 string thành 1 table nhưng đặt biệt chuổi này phải có 1 qui luật để tách. Ví dụ : Cho một chuổi String programmingLanguague = "JAVA,C#,JAVASCRIPT,PYTHON,RUBY,GOLANG" , nhiệm vụ chúng ta tách thành 1 table với column là ProgramLanguage. Phân tích ta thấy rằng trong chuổi string programmingLanguague mỗi ngôn ngữ lập trình tách nhau bởi dấu ',' nên điều kiện tách là dấu ",".

-- kHỞI TẠO CHUỔI STRING @programmingLanguague
DECLARE @programmingLanguague NVARCHAR(MAX)
SET @programmingLanguague ='JAVA,C#,JAVASCRIPT,PYTHON,RUBY,GOLANG'
-- TÁCH CHUỔI STRING THÀNH TABLE
SELECT A.VALUE AS ProgramLanguage
FROM STRING_SPLIT(@programmingLanguague,',') A

Kết quả :

• CROSS APPLY : Là một kiểu Join khá đặc biệt trong SQL.

Để dễ hiểu mình có 1 ví dụ như thế này, chúng ta tạo ra 2 table bao gồm Colors, và items như sau.

CREATE TABLE [dbo].[Colors](
    Id UNIQUEIDENTIFIER NOT NULL,
    Color NVARCHAR(256) NOT NULL
    CONSTRAINT Id_Color PRIMARY KEY
)
CREATE TABLE [dbo].[Items](
    Id UNIQUEIDENTIFIER NOT NULL,
    Item NVARCHAR(256) NOT NULL
    CONSTRAINT Id_Item PRIMARY KEY
)
INSERT INTO [dbo].[Colors] 
VALUES 
(NEWID(),'Red'),
(NEWID(),'Orange'),
(NEWID(),'Yellow'),
(NEWID(),'Green'),
(NEWID(),'Blue'),
(NEWID(),'Black'),
(NEWID(),'White'),
(NEWID(),'Pink'),
(NEWID(),'Brown'),
(NEWID(),'Gray'),
(NEWID(),'Purple')

INSERT INTO [dbo].[Items] 
VALUES 
(NEWID(),N'ÁO'),
(NEWID(),N'Xe máy'),
(NEWID(),N'Xe ô tô'),
(NEWID(),N'Quần')

Nhiệm vụ chúng ta là với mỗi Item sẻ có tất cả các màu trong table Colors.

SELECT [Item]
      ,c.[Color]
FROM [dbo].[Items] AS i
CROSS APPLY [dbo].[Colors] AS c

Kết quả :

• Ví dụ nâng cao ứng dụng STRING_SPLIT và CROSS APPLY.

Yêu cầu: Tạo table chứa tất các thời gian trong một ngày như sau. Thời gian bắt đầu 1 ngày : 00h : 00m : 00 s tiếp đến giây tiếp theo 00h : 00m : 01 s và cho đến hết ngày ....... -> 23h : 59m : 58s -> 23h : 59m : 59s.

Đầu tiên chúng ta tạo table TimeDay bao gồm các column (Hour, minute, second)

CREATE TABLE [dbo].TimeDay(
    Id VARCHAR(64) PRIMARY KEY,
    Hour   VARCHAR(64) NOT NULL,
    Minute VARCHAR(64) NOT NULL,
    Second VARCHAR(64) NOT NULL
)

Tạo Store PROCEDURE để insert data vào table [dbo].TimeDay. Kết hợp với string_split và cross apply

CREATE OR ALTER PROCEDURE [dbo].[Insert_TimeInDay]
AS 
BEGIN

    DECLARE @Hour INT;   SET @Hour = 24;   DECLARE @HourDataArray   NVARCHAR(MAX);
    DECLARE @Minute INT; SET @Minute = 60; DECLARE @MinuteDataArray NVARCHAR(MAX);
    DECLARE @Second INT; SET @Second = 60; DECLARE @SecondDataArray NVARCHAR(MAX);
    DECLARE @indexHour INT;   SET @indexHour = 0;
    DECLARE @indexMinute INT; SET @indexMinute = 0;
    DECLARE @indexSecond INT; SET @indexSecond = 0;
    -- Tạo data @HourDataArray có dạng = {01,02,03,....,23}
    WHILE @indexHour < @Hour 
    BEGIN

        IF @indexHour < 10
            -- add 0 before number from 1->9
            SET @HourDataArray = CONCAT(@HourDataArray ,'0'+ CONVERT(NVARCHAR(64),@indexHour) +',');
        ELSE
            IF @indexHour = @Hour -1
                -- remove key ',' at last index 
                SET @HourDataArray = @HourDataArray + CONVERT(NVARCHAR(64),@indexHour);
            ELSE
                SET @HourDataArray = @HourDataArray+ CONVERT(NVARCHAR(64),@indexHour) +',';
        SET @indexHour = @indexHour +1;
    END
    -- Tạo data @MinuteDataArray có dạng = {01,02,03,04,.......,57,58,59}
    WHILE @indexMinute < @Minute 
    BEGIN
        IF @indexMinute < 10
             -- add 0 before number from 1->9
            SET @MinuteDataArray = CONCAT(@MinuteDataArray, '0'+ CONVERT(NVARCHAR(64),@indexMinute)+',');
        ELSE
            IF @indexMinute = @Minute -1
                -- remove key ',' at last index 
                SET @MinuteDataArray = @MinuteDataArray + CONVERT(NVARCHAR(64),@indexMinute);
            ELSE 
                SET @MinuteDataArray = @MinuteDataArray + CONVERT(NVARCHAR(64),@indexMinute)+',';
        SET @indexMinute = @indexMinute +1
    END

    -- Tạo data @SecondDataArray có dạng = {01,02,03,04,.......,57,58,59}
    WHILE @indexSecond < @Second 
    BEGIN
        IF @indexSecond < 10
            -- add 0 before number from 1->9
            SET @SecondDataArray = CONCAT(@SecondDataArray, '0'+ CONVERT(NVARCHAR(64),@indexSecond)+',');
        ELSE
            IF @indexSecond = @Second -1
                -- remove key ',' at last index 
                SET @SecondDataArray = @SecondDataArray + CONVERT(NVARCHAR(64),@indexSecond);
            ELSE 
                SET @SecondDataArray = @SecondDataArray + CONVERT(NVARCHAR(64),@indexSecond)+',';
        SET @indexSecond = @indexSecond +1
    END
    -- insert data vào table [dbo].[TimeDay]
    INSERT INTO [dbo].[TimeDay](Id,[Hour],[Minute],[Second])
    SELECT h.value+m.value+s.value AS Id,
           h.value as Hour,
           m.value as Minute,
           s.value as Second
    FROM STRING_SPLIT(@HourDataArray, ',') h
    CROSS APPLY STRING_SPLIT(@MinuteDataArray, ',') m
    CROSS APPLY STRING_SPLIT(@SecondDataArray, ',') s
END

Chạy run PROCEDURE [dbo].[Insert_TimeInDay]

EXECUTE [dbo].[Insert_TimeInDay]
SELECT * FROM [dbo].[TimeDay]

Kết quả :