时间戳

首页 > 解决方案 > 将空格添加到带有 Caps 的 Camelcase

sql-server - 将空格添加到带有 Caps 的 Camelcase

问题描述

我有一个以驼峰形式传递字符串的外部应用程序。用户希望报告字符串并希望将其格式化为句子。字符串中可能包含我需要在周围添加空格的所有大写字母。

在这里根据这个问题构建了一个函数：Is there any SQL Server built-in function to convert string in camel case? 但是，它不适用于全大写单词。下面是我的修改版。


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;

我从应用程序中获得的示例 - “ThisIsASentenceWithCAPTIInIt”

我想回传的内容——“这是一个有 CAPTI 的句子”

我从修改后的版本中得到了什么——“这是带有 CAPTIIn It 的句子”

标签： sql-servertsqlcamelcasing

解决方案

这是我的建议：

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;

简而言之：

我们使用递归 CTE。简单的方法只是逐个字符地读取字符串，GrowingString并在最后返回整个字符串。这将与原始字符串相同。

棘手的部分是：在哪里插入空格？

如果当前字母不是大写，而下一个是大写，我们需要一个空格。这是肯定的。此外，嵌入式CASE将测试当前和下一个字母是否大写，但第三个字母是否大写。在这种情况下，我们也需要空间。

推荐阅读