去除重复的重复字符 [英] Removing repeated duplicated characters

问题描述

我的存储过程中有一个字符串，例如 ',,,sam,,bob,' 或 ',,,'从上面的字符串我必须从中删除多个逗号，它必须看起来像'sam,bob,' 或仅当 ',,,' 然后 '' .我必须只使用 Sql Server 函数.我使用的是 Sql Server 2008 和 .Net 3.5

I have a string in my stored proc like ',,,sam,,bob,' or ',,,' from the above string I have to delete multiple commas from it, it must look like 'sam,bob,' or only if ',,,' then '' . I must use only Sql Server Functions. Im using Sql Server 2008 and .Net 3.5

提前致谢.

推荐答案

这适用于纯逗号或最多 398 个连续逗号的字符串.

This works for strings that are exclusively commas or have up to 398 contiguous commas.

 SELECT 
     CASE 
         WHEN TargetString NOT LIKE '%[^,]%' 
             THEN '' /*The string is exclusively commas*/
         ELSE 
            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TargetString,
            REPLICATE(',',16),','), /*399/16 = 24 remainder 15*/
            REPLICATE(',',8),','),  /* 39/ 8 =  4 remainder 7*/
            REPLICATE(',',4),','),  /* 11/ 4 =  2 remainder 3*/
            REPLICATE(',',2),','),  /*  5/ 2 =  2 remainder 1*/
            REPLICATE(',',2),',')   /*  3/ 2 =  1 remainder 1*/
         END
 FROM T    

如果需要更多，请在顶部添加额外的 2 次幂，如果需要更少，则从顶部移除.每个阶段的评论表示该阶段不会成功处理的最小数字.

Add extra powers of 2 at the top if you need more or remove from the top if you need less. The comments by each stage indicate the smallest number that this stage will not deal with successfully.

所有注释行都是这种格式

All the comment lines are in this format

/*  L/D    =  Q remainder R */

D:    Corresponds to the length of the string generated by `REPLICATE`
R:    Is always D-1
Q+R:  Form L for the next step

所以用另一个 REPLICATE(',',32),',') 阶段向上扩展系列

So to extend the series upwards with another REPLICATE(',',32),',') stage

D = 32 
R = 31
Q = 368 (399-31)
L = (368 * 32) + 31 = 11807

这样可以处理最多 11,806 个字符的逗号部分.

So that would deal with sections of commas up to 11,806 characters.

这篇关于去除重复的重复字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！