Add spaces to Camelcase with Caps in it

Question

I have an external app passing strings in camelcase. Users are looking to report on the string and want it formatted as a sentence. The string may have words in it with all caps that I need to add spaces around.

built out a function based on this question here: Is there any SQL Server built-in function to convert string in camel case? however, it does not work with the all caps words. Below is my modified version.

DECLARE @String    NVARCHAR(MAX) = 'ThisIsASentenceWithCAPTIInIt'
    --, @Len       INT         --  = LEN(@String)
      , @Delimiter CHAR(1)       = ' '
      , @Iterator  INT           = 2; --Don't put space to left of first even if it's a capital

WHILE @Iterator <= LEN(@String)
-- Iterate through string
    BEGIN
        -- Check if current character is Uppercase (lowercase = 0)
        IF PATINDEX('[ABCDEFGHIJKLMNOPQRSTUVWXYZ]', SUBSTRING(@String, @Iterator, 1) COLLATE Latin1_General_CS_AI) <> 0
        -- Do this if capital
            BEGIN
                -- check if the previous character is lowercase, if it is then add a space before the current character.
                IF(
                   (PATINDEX('[ABCDEFGHIJKLMNOPQRSTUVWXYZ]', SUBSTRING(@String, @Iterator - 1, 1) COLLATE Latin1_General_CS_AI) = 0
                    AND SUBSTRING(@String, @Iterator - 1, 1) <> ' '
                   )
                   OR PATINDEX('[ABCDEFGHIJKLMNOPQRSTUVWXYZ]', SUBSTRING(@String, @Iterator + 1, 1) COLLATE Latin1_General_CS_AI) = 0)
                    BEGIN
                        SET @String = STUFF(@String, @Iterator, 0, @Delimiter);
                        SET @Iterator+=1;
                END;
                -- check if the next character is lowercase, if it is then add a space before the current character.
                SET @Iterator+=1;
        END;
        ---- Don't care about current character being lowercase. Just continue iterating
        SET @Iterator+=1;
    END;

SELECT @String;

Example of what I get from the app - "ThisIsASentenceWithCAPTIInIt"

What I want to pass back - "This Is A Sentence With CAPTI In It"

What I am getting back from my modified version - "This Is ASentence With CAPTIIn It"

Answer 1

This is my suggestion:

DECLARE @s VARCHAR(100)='ThisIsASentenceWithCAPTIInIt';

WITH cte AS
(
    SELECT 1 AS Position
          ,@s AS Original
          ,CAST(SUBSTRING(@s,1,1) AS VARCHAR(MAX)) AS GrowingString
    UNION ALL
    SELECT cte.Position+1
          ,cte.Original
          ,CONCAT(cte.GrowingString 
          ,CurrentLetter
          ,CASE WHEN CurrentCapit=0 AND NextCapit=1 THEN ' ' ELSE 
                CASE WHEN CurrentCapit=1 AND NextCapit=1 AND ThirdCapit=0 THEN ' ' ELSE '' END END ) AS GrowingString
    FROM cte
    CROSS APPLY(SELECT SUBSTRING(@s,cte.Position+1,1) CurrentLetter
                      ,SUBSTRING(@s,cte.Position+2,1) NextLetter
                      ,SUBSTRING(@s,cte.Position+3,1)ThirdLetter) A
    CROSS APPLY(SELECT CASE WHEN ASCII(CurrentLetter) BETWEEN 65 AND 90 THEN 1 ELSE 0 END CurrentCapit
                      ,CASE WHEN ASCII(NextLetter) BETWEEN 65 AND 90 THEN 1 ELSE 0 END NextCapit
                      ,CASE WHEN ASCII(ThirdLetter) BETWEEN 65 AND 90 THEN 1 ELSE 0 END ThirdCapit) B
    WHERE cte.Position < LEN(@s)
)
SELECT TOP 1 GrowingString
FROM cte
ORDER BY Position DESC;

The idea in short:

We use a recursive CTE. The simple approach will just read the string character by character and will return the whole string in GrowingString at the end. This would be just the same as the original string.

The tricky part is: Where to insert spaces?

If the current letter is not capitalized and the next is, we need a space. That's for sure. Furthermore, the embedded CASE will test if the current and the next letter is capitalized but the third one. In this case we need the space too.

Answer 2

doing it this in SQL Server is not the best choice. also rewriting existing string can be challenging somewhat challenging.

I think there are many more way to simplify this code, but here is a version that I believe will achieve the result you are looking for.

DECLARE @String    NVARCHAR(MAX) = 'ThisIsASentenceWithCAPTIInIt'
    --, @Len       INT         --  = LEN(@String)
      , @Delimiter CHAR(1)       = ' '
      , @Iterator  INT           = 1; --Don't put space to left of first even if it's a capital

DECLARE @retval nvarchar(max) = '';

WHILE @Iterator <= LEN(@String)
-- Iterate through string
    BEGIN
        -- Check if current character is Uppercase (lowercase = 0)
        IF PATINDEX('[ABCDEFGHIJKLMNOPQRSTUVWXYZ]', SUBSTRING(@String, @Iterator, 1) COLLATE Latin1_General_CS_AI) <> 0
        -- Do this if capital
            BEGIN
                -- check if the previous character is lowercase, if it is then add a space before the current character.
                IF(
                   (PATINDEX('[ABCDEFGHIJKLMNOPQRSTUVWXYZ]', SUBSTRING(@String, @Iterator - 1, 1) COLLATE Latin1_General_CS_AI) = 0
                    AND SUBSTRING(@String, @Iterator - 1, 1) <> ' '
                   )
                   OR PATINDEX('[ABCDEFGHIJKLMNOPQRSTUVWXYZ]', SUBSTRING(@String, @Iterator + 1, 1) COLLATE Latin1_General_CS_AI) = 0)
                    BEGIN
                        set @retval += ' ' ;
                        --SET @String = STUFF(@String, @Iterator, 0, @Delimiter);
                        --SET @Iterator+=1;
                END;
                -- check if the next character is lowercase, if it is then add a space before the current character.
                --Set @retval += SUBSTRING(@String, @Iterator, 1);
                --SET @Iterator+=1;
            END;
        ---- Don't care about current character being lowercase. Just continue iterating
        Set @retval += SUBSTRING(@String, @Iterator, 1);
        SET @Iterator+=1;
    END;
SET @retval = Ltrim(@retval);
SELECT @retval;