Add space before and after all numbers in an alphanumeric string in SQL

Question

Add space before and after all numbers in an alphanumeric string in SQL

Example:

aa01bb03cc -> aa 01 bb 03 cc

aa nk 0221ed23xyz op09 yy -> aa nk 0221 ed 23 xyz op 09 yy

Answer 1

I've came up with this approach:

CREATE FUNCTION dbo.WhitespaceNumbers (
    @string VARCHAR(MAX)
    )
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @position0 INT = 0
        , @position1 INT = 0
        , @position2 INT = 0;

    WHILE @string LIKE '%[^ 0-9][0-9]%' OR @string LIKE '%[0-9][^ 0-9]%'
    BEGIN
        SET @position1 = PATINDEX('%[^ 0-9][0-9]%', @string);
        SET @position2 = PATINDEX('%[0-9][^ 0-9]%', @string);

        SET @position0 = (
            SELECT MIN(position)
            FROM (VALUES (@position1), (@position2)) AS T(position)
            WHERE T.position > 0
            );

        SET @string = STUFF(@string, @position0 + 1, 0, ' ');
    END

    RETURN @string;
END

It does find the minimum position that doesn't match one of these patterns and adds a whitespace after it:

• %[^ 0-9][0-9]% - something before number except number or whitespace
• %[0-9][^ 0-9]% - something after number except number or whitespace

And then adds a space after it, then continues to loop.
I'm making a T.position > 0 check because if there's just one pattern that matches, @position0 is set to 0 and it will run infintely.

Results are as expected in your query:

PRINT dbo.WhitespaceNumbers('aa01bb03cc');
aa 01 bb 03 cc

PRINT dbo.WhitespaceNumbers('aa nk 0221ed23xyz op09 yy');
aa nk 0221 ed 23 xyz op 09 yy

Keep in mind that this is quite raw and could be simplified and wrapped in a function to encapsulate logic.

I also would encourage you to apply following logic at application level, not database (if possible). SQL Server doesn't perform well at string manipulation.

Update

Made some code changes. This looks a bit more elegant and does exactly the same

CREATE FUNCTION dbo.WhitespaceNumbers (@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @position INT;

    WHILE 1 = 1
    BEGIN
        SET @position = (
            SELECT MIN(position)
            FROM (VALUES (PATINDEX('%[^ 0-9][0-9]%', @string)), (PATINDEX('%[0-9][^ 0-9]%', @string))) AS T(position)
            WHERE T.position > 0
            );

        IF @position IS NULL
            BREAK;

        SET @string = STUFF(@string, @position + 1, 0, ' ');
    END

    RETURN @string;
END