文章/答案/技术大牛

发布

问SQL字符替换函数扩展
EN

Stack Overflow用户

提问于 2014-10-23 07:08:16

回答 3查看 150关注 0票数 2

我目前正在开发一个程序，我想编写一个函数，它接受以下格式的值

"AAAA BBBB"  CCCC DDDD EEEE "FFFF GGGG HHHH"

我想用"_“替换上面的空格，需要输出如下格式(请注意，这应该只发生在双引号内的字符串中)

"AAAA_BBBB"  CCCC DDDD EEEE "FFFF_GGGG_HHHH"

所以I got the correct answer to this in my previous question

DECLARE @In VARCHAR(50) = '"AAAA BBBB"  CCCC DDDD EEEE "FFFF GGGG HHHH"'

DECLARE @Quote SMALLINT = -1, @Index INT = 1, @Char CHAR(1)
WHILE @Index <= LEN(@In) BEGIN
    SET @Char = SUBSTRING(@In, @Index, 1)
    IF @Char = '"'
        SET @Quote = @Quote * -1
    IF @Char = ' ' AND @Quote > 0
        SET @In = STUFF(@In, @Index, 1, '_')
    SET @Index = @Index + 1
END

PRINT @In

现在我想用下面的要求来修改它

使用所需的格式，您可以注意到，下划线应该替换为仅位于所附双引号内的空格，但如果用户输入的内容如下，则现在我得到了错误的输出

"AAAA BBBB"  CCCC DDDD EEEE "FFFF GGGG HHHH cccc

它给出的输出

"AAAA_BBBB"  CCCC DDDD EEEE "FFFF_GGGG_HHHH_cccc

但是它必须只为第一个封闭的双引号提供输出，因为另一个没有关闭，预期的输出应该如下所示

"AAAA_BBBB"  CCCC DDDD EEEE "FFFF GGGG HHHH cccc

sql-function

sql

sql-server

replace

回答 3

Stack Overflow用户

回答已采纳

发布于 2014-10-23 07:47:07

请参考这段代码。我想这段代码会给出一些提示。此代码不是经过测试的代码。我张贴答案，因为至少你得到了一些提示，以找到你的解决方案。

DECLARE @In VARCHAR(50) = '"AAAA BBBB"  CCCC DDDD EEEE "FFFF GGGG HHHH IIIII'

DECLARE @Quote SMALLINT = -1, @Index INT = 1, @Char CHAR(1) ,@New_Char CHAR(100)
WHILE @Index <= LEN(@In) BEGIN
    SET @Char = SUBSTRING(@In, @Index, 1)
    IF @Char = '"'
        SET @Quote = @Quote * -1
        SET @New_Char = SUBSTRING(@In, @Index + 1,213)
    IF @Char = ' ' AND @Quote > 0 AND @New_Char like '%"%'
        SET @In = STUFF(@In, @Index, 1, '_')
    SET @Index = @Index + 1
END

PRINT @In

票数 2

Stack Overflow用户

发布于 2014-10-23 11:50:28

以下是我要做的修改

一旦完成了所有替换，退出就会存在循环(添加了WHILE循环条件，以及引号识别的逻辑如何操作以方便退出)
删除任何长度的依赖项(CHAR(100)，SUBSTRING(...,213))

码

DECLARE @In VARCHAR(50) = '"AAAA BBBB"  CCCC DDDD EEEE "FFFF GGGG HHHH IIIII"'

DECLARE @Quote SMALLINT = 0, @Index INT = 1, @Char CHAR(1)
WHILE @Quote < 2 AND @Index <= LEN(@In) BEGIN
    SET @Char = SUBSTRING(@In, @Index, 1)
    IF @Char = '"'
        SET @Quote = @Quote + 1
    IF @Char = ' ' AND @Quote = 1
        SET @In = STUFF(@In, @Index, 1, '_')
    SET @Index = @Index + 1
END

PRINT @In

输出

"AAAA_BBBB"  CCCC DDDD EEEE "FFFF GGGG HHHH IIIII"

票数 2

Stack Overflow用户

发布于 2014-10-23 08:04:44

我会以不同的方式对待你的最后一个答案，并避免一个循环。第一步是生成一个包含50个序列号的列表(这个数字应该与varchar的长度相匹配)。这将允许您将varchar拆分为更易于处理的行：

DECLARE @s VARCHAR(50) = '"AAAA_BBBB"  CCCC DDDD EEEE "FFFF GGGG HHHH cccc';

WITH Numbers (Number) AS -- 50 ROWS
(   SELECT  TOP (LEN(@s)) ROW_NUMBER() OVER(ORDER BY N1.N)
    FROM    (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) N1 (N)
            CROSS JOIN (VALUES (1), (1), (1), (1), (1)) N2 (N)
)
SELECT  Number,
        Letter = SUBSTRING(@s, Number, 1)
FROM    Numbers;

这提供了如下内容：

Number | Letter
-------+--------
   1   |   "
   2   |   A
   3   |   A

以类似的方式，您可以使用它获得“组”引号：

DECLARE @s VARCHAR(50) = '"AAAA_BBBB"  CCCC DDDD EEEE "FFFF GGGG HHHH cccc';

WITH Numbers (Number) AS -- 50 ROWS
(   SELECT  TOP (LEN(@s)) ROW_NUMBER() OVER(ORDER BY N1.N)
    FROM    (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) N1 (N)
            CROSS JOIN (VALUES (1), (1), (1), (1), (1)) N2 (N)
)
SELECT  Number,
        GroupID = CEILING(ROW_NUMBER() OVER(ORDER BY Number) / 2.0)
FROM    Numbers 
WHERE   SUBSTRING(@s, Number, 1) = '"'

这意味着：

Number | GroupID
-------+--------
   1   |    1
  11   |    1
  29   |    2

您可以通过GroupID来获得引号组的开始和结束位置：

DECLARE @s VARCHAR(50) = '"AAAA_BBBB"  CCCC DDDD EEEE "FFFF GGGG HHHH cccc';

WITH Numbers (Number) AS -- 50 ROWS
(   SELECT  TOP (LEN(@s)) ROW_NUMBER() OVER(ORDER BY N1.N)
    FROM    (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) N1 (N)
            CROSS JOIN (VALUES (1), (1), (1), (1), (1)) N2 (N)
), Grouping AS
(   SELECT  Number,
            GroupID = CEILING(ROW_NUMBER() OVER(ORDER BY Number) / 2.0)
    FROM    Numbers 
    WHERE   SUBSTRING(@s, Number, 1) = '"'
)
SELECT  StartPosition = MIN(Number),
        EndPosition = MAX(Number)
FROM    Grouping
GROUP BY GroupID
HAVING COUNT(*) = 2 -- ONLY INCLUDE WHERE THERE IS A START AND AN END

现在，您可以返回到原来的拆分，并将此范围内的任何空格替换为下划线：

DECLARE @s VARCHAR(50) = '"AAAA_BBBB"  CCCC DDDD EEEE "FFFF GGGG HHHH cccc';

WITH Numbers (Number) AS -- 50 ROWS
(   SELECT  TOP (LEN(@s)) ROW_NUMBER() OVER(ORDER BY N1.N)
    FROM    (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) N1 (N)
            CROSS JOIN (VALUES (1), (1), (1), (1), (1)) N2 (N)
), Letters AS
(   SELECT  Number,
            Letter = SUBSTRING(@s, Number, 1)
    FROM    Numbers
), Grouping AS
(   SELECT  Number,
            GroupID = CEILING(ROW_NUMBER() OVER(ORDER BY Number) / 2.0)
    FROM    Letters 
    WHERE   Letter = '"'
), Groups AS
(   SELECT  StartPosition = MIN(Number),
            EndPosition = MAX(Number)
    FROM    Grouping
    GROUP BY GroupID
    HAVING COUNT(*) = 2
)
SELECT  l.Number,
        Letter = CASE WHEN g.StartPosition IS NOT NULL AND l.Letter = ' ' THEN '_' ELSE l.Letter END
FROM    Letters AS l
        LEFT JOIN Groups AS g
            ON g.StartPosition <= l.Number
            AND g.EndPosition >= l.Number;

这意味着：

Number | Letter
-------+--------
   1   |   "
   2   |   A
   3   |   A
   4   |   A
   5   |   A
   6   |   _
   7   |   B
   8   |   B
   9   |   B
  10   |   B
  11   |   "
  12   |

然后可以使用FOR XML concatenation重新生成原始字符串。

DECLARE @s VARCHAR(50) = '"AAAA_BBBB"  CCCC DDDD EEEE "FFFF GGGG HHHH cccc';

WITH Numbers (Number) AS -- 50 ROWS
(   SELECT  TOP (LEN(@s)) ROW_NUMBER() OVER(ORDER BY N1.N)
    FROM    (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) N1 (N)
            CROSS JOIN (VALUES (1), (1), (1), (1), (1)) N2 (N)
), Letters AS
(   SELECT  Number,
            Letter = SUBSTRING(@s, Number, 1)
    FROM    Numbers
), Grouping AS
(   SELECT  Number,
            GroupID = CEILING(ROW_NUMBER() OVER(ORDER BY Number) / 2.0)
    FROM    Letters 
    WHERE   Letter = '"'
), Groups AS
(   SELECT  StartPosition = MIN(Number),
            EndPosition = MAX(Number)
    FROM    Grouping
    GROUP BY GroupID
    HAVING COUNT(*) = 2
)
SELECT  String = (  SELECT  CASE WHEN g.StartPosition IS NOT NULL AND l.Letter = ' ' THEN '_' ELSE l.Letter END
                    FROM    Letters AS l
                            LEFT JOIN Groups AS g
                                ON g.StartPosition <= l.Number
                                AND g.EndPosition >= l.Number
                    ORDER BY l.Number
                    FOR XML PATH(''), TYPE
                ).value('.', 'VARCHAR(50)');

最后，我将把逻辑包含在内联表值函数中：

CREATE FUNCTION dbo.YourFunctionName (@s VARCHAR(50))
RETURNS TABLE
AS
RETURN
(   WITH Numbers (Number) AS -- 50 ROWS
    (   SELECT  TOP (LEN(@s)) ROW_NUMBER() OVER(ORDER BY N1.N)
        FROM    (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) N1 (N)
                CROSS JOIN (VALUES (1), (1), (1), (1), (1)) N2 (N)
    ), Letters AS
    (   SELECT  Number,
                Letter = SUBSTRING(@s, Number, 1)
        FROM    Numbers
    ), Grouping AS
    (   SELECT  Number,
                GroupID = CEILING(ROW_NUMBER() OVER(ORDER BY Number) / 2.0)
        FROM    Letters 
        WHERE   Letter = '"'
    ), Groups AS
    (   SELECT  StartPosition = MIN(Number),
                EndPosition = MAX(Number),
                Letter = ' '
        FROM    Grouping
        GROUP BY GroupID
        HAVING COUNT(*) = 2
    )
    SELECT  String = (  SELECT  CASE WHEN l.Letter = g.Letter THEN '_' ELSE l.Letter END
                        FROM    Letters AS l
                                LEFT JOIN Groups AS g
                                    ON g.StartPosition <= l.Number
                                    AND g.EndPosition >= l.Number
                        ORDER BY l.Number
                        FOR XML PATH(''), TYPE
                    ).value('.', 'VARCHAR(50)')
);

它可以称为：

SELECT  t.s,
        Replaced = (SELECT String FROM dbo.YourFunctionName (t.s))
FROM    (VALUES 
            ('"AAAA_BBBB" CCCC DDDD EEEE "FFFF GGGG HHHH cccc'),
            ('"AAAA_BBBB" CCCC DDDD EEEE "FFFF GGGG HHHH cccc"'),
            ('"AAAA_BBBB" CCCC "DDDD EEEE" "FFFF GGGG HHHH cccc'),
            ('"AAAA_BBBB" CCCC "DDDD EEEE" "FFFF GGGG HHHH cccc"')
        ) AS t (s);

这看起来可能要复杂得多，但我希望我已经正确地解释了逻辑，并且使用内联表值函数的最大优点是将定义扩展到主查询中，不像scalar有类似于游标的执行。

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/26522911

复制

相似问题

问SQL字符替换函数扩展
EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL字符替换函数扩展EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL字符替换函数扩展
EN