Moving Comma‑Delimited Keywords into a Proper SQL Table

In a legacy application, keyword data was stored in a single SQL Server field as a comma‑delimited string. This made searching, filtering, and maintaining keywords difficult. To improve data integrity and make keyword management easier, each keyword needed to be extracted and inserted into a dedicated Keywords table.

The solution below loops through each provider, reads its comma‑separated keywords, splits them, trims them, and inserts each keyword as an individual row with its associated provider ID.

SQL Script to Extract and Insert Keywords

DECLARE @p_id int,
        @keywords nvarchar(max),
        @strKeywords nvarchar(max),
        @pos int,
        @keyword nvarchar(255);

DECLARE my_cursor CURSOR FOR
    SELECT id, [program key words]
    FROM Providers;

OPEN my_cursor;
FETCH NEXT FROM my_cursor INTO @p_id, @keywords;

WHILE @@FETCH_STATUS <> -1
BEGIN
    SET @strKeywords = @keywords;

    -- Ensure the string ends with a comma
    IF SUBSTRING(@strKeywords, LEN(@strKeywords) - 1, 1) <> ','
        SET @strKeywords = @strKeywords + ',';

    SET @pos = 0;

    -- Loop through each comma-delimited keyword
    WHILE CHARINDEX(',', @strKeywords) > 0
    BEGIN
        SET @keyword = CAST(SUBSTRING(@strKeywords, 0, CHARINDEX(',', @strKeywords)) AS nvarchar(255));
        SET @keyword = LTRIM(RTRIM(@keyword));

        INSERT INTO Keywords (provider_id, keyword)
        VALUES (@p_id, @keyword);

        SET @strKeywords = SUBSTRING(@strKeywords, CHARINDEX(',', @strKeywords) + 1, LEN(@strKeywords) - @pos);
    END

    FETCH NEXT FROM my_cursor INTO @p_id, @keywords;
END

CLOSE my_cursor;
DEALLOCATE my_cursor;

This script successfully migrates comma‑delimited keyword data into a normalized table structure. Each keyword becomes its own row, making future edits, deletions, and searches significantly easier.

Although modern SQL Server versions offer cleaner string‑splitting functions (such as STRING_SPLIT), this cursor‑based approach remains effective for older databases or environments where newer functions are not available.