SQL Server 2014：如何为Column1中逗号后无空格的内容添加空格

Got it, let's work through this problem together. You need to tweak values in Column1 so that any comma followed immediately by a non-space character gets a space inserted between them—like turning abc,def into abc, def—while leaving already correct formats (such as abc, def) untouched. Since SQL Server 2014 doesn't have native regex replace, we've got two solid approaches to get this done:

Option 1: Nested REPLACE (Quick & Simple for Most Cases) #

This method uses nested REPLACE functions to handle the formatting in a straightforward way:

1. First, replace every comma with , (comma + space). This fixes unformatted commas but turns existing correct ones into , (comma + two spaces).
2. Then, replace any instances of , back to , to clean up duplicates.

-- Test first to verify results
SELECT 
    Column1,
    REPLACE(REPLACE(Column1, ',', ', '), ',  ', ', ') AS UpdatedColumn
FROM YourTableName
WHERE Column1 LIKE '%,[^ ]%'; -- Target only rows with commas missing spaces

-- Once you confirm the output is correct, run the update
UPDATE YourTableName
SET Column1 = REPLACE(REPLACE(Column1, ',', ', '), ',  ', ', ')
WHERE Column1 LIKE '%,[^ ]%';

Pro tip: Add an extra layer of REPLACE(', ', ', ') if you have cases where multiple commas without spaces are stacked (like abc,def,ghi,jkl—though two layers should handle most everyday scenarios).

Option 2: Custom User-Defined Function (UDF) for Full Flexibility #

If your data has lots of comma-separated values or complex patterns, a UDF will handle all cases reliably, no matter how many times the formatting issue occurs.

First, create the function:

CREATE FUNCTION dbo.AddSpaceAfterCommas(@InputString NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @CommaPosition INT = CHARINDEX(',', @InputString);

    -- Loop through every comma in the string
    WHILE @CommaPosition > 0
    BEGIN
        -- Check if the character after the comma isn't a space
        IF @CommaPosition < LEN(@InputString) 
           AND SUBSTRING(@InputString, @CommaPosition + 1, 1) != ' '
        BEGIN
            -- Insert a space right after the comma
            SET @InputString = STUFF(@InputString, @CommaPosition + 1, 0, ' ');
            -- Move position forward by 2 (since we added a space)
            SET @CommaPosition = @CommaPosition + 2;
        END
        ELSE
        BEGIN
            -- Jump to the next comma
            SET @CommaPosition = CHARINDEX(',', @InputString, @CommaPosition + 1);
        END
    END

    RETURN @InputString;
END

Then use it to update your column:

-- Test first to ensure accuracy
SELECT 
    Column1,
    dbo.AddSpaceAfterCommas(Column1) AS UpdatedColumn
FROM YourTableName
WHERE Column1 LIKE '%,[^ ]%';

-- Execute the update once tests pass
UPDATE YourTableName
SET Column1 = dbo.AddSpaceAfterCommas(Column1)
WHERE Column1 LIKE '%,[^ ]%';

Important Notes #

• Always test with a SELECT statement first before running an UPDATE—you don't want to accidentally mess up your data!
• The WHERE clause filters only rows that need changes, which saves unnecessary updates and boosts performance.
• For large tables, the nested REPLACE method might be faster, while the UDF is better for complex or highly variable data.

内容的提问来源于stack exchange，提问作者Cody Mayers