如何在sql查询中查找包含三个以上重复char ex：（Acabacaga）的输入 [英] how to find a input contains more than three repeating char ex:(Acabacaga) in sql query

问题描述

大家好，





我想找一个输入包含三个以上的重复字符，请建议我查询。



ex：



输入= Acabacaga



i希望restict输入shuld不包含超过三个重复的letter.upto 2 dullicate chr是好的..但是我不会收到三个或更多的dulicate字母。









提前致谢。



Rajshree V Lande。

hello guys,


I want to find A input contains more than three repeating chars,please suggest me query for that.

ex:

Input=Acabacaga

i want restict the input shuld not containing more than three duplicate letter.upto 2 dullicate chr is ok..but i shold not contatin three or more dulicate letters.




Thanks in advance.

Rajshree V Lande.

推荐答案

试试这个：

Try this:

DECLARE @input VARCHAR(30) = 'Acabacaga'

;WITH CTE AS
(
    SELECT CONVERT(VARCHAR(1),LEFT(@input,1)) AS Letter, RIGHT(@input, LEN(@input)-1) AS Remainder
    WHERE LEN(@input)>1
    UNION ALL
    SELECT CONVERT(VARCHAR(1),LEFT(Remainder,1)) AS Letter, RIGHT(Remainder, LEN(Remainder)-1) AS Remainder
    FROM CTE
    WHERE LEN(Remainder)>1
    UNION ALL
    SELECT CONVERT(VARCHAR(1),Remainder) AS Letter, NULL AS Remainder
    FROM CTE
    WHERE LEN(Remainder)=1
)
SELECT Letter, ASCII(Letter) AS CharCode, COUNT(Letter) AS CountOfLetter
FROM CTE
GROUP BY Letter, ASCII(Letter)
HAVING COUNT(Letter)>2

结果：

Result:

Letter	CharCode	CountOfLetter
a			97			4

如需了解更多信息，请参阅：常用表格表达式 [ ^ ]。换句话说，CTE摇滚！

For further information, please see: Common Table Expressions[^]. In other words, CTE rocks!

嗨Rajshree，



你可以尝试这个

Hi Rajshree,

You can try this

SELECT len('acabacaga') - len(replace('acabacaga','a','')) As NoOfTimesACharacterIsRepeated.

您可以将其输出存储在变量中并传递字符串类型和要作为变量匹配的字符。



问候，

Praneet

You can store its output in a variable and pass the string type and the character to match as variables.

Regards,
Praneet

ALTER FUNCTION [dbo]。[CheckValidString]

（

@input varchar（50）



）

退货varchar

AS

BEGIN



声明@inputLength integer = datalength（@input）;

声明@intIndex integer = 1;

声明@intMaxCount整数= 3;

声明@singleChar字符（1）;

声明@isValid字符（1）;

而（1 = 1）

开始

如果@intIndex> @inputLength

开始

选择@isValid ='Y';

休息;

结束



选择@singleChar = substring（@input，@ intIndex，1）



if（@inputLength - datalength（replace （@input，@ singleChar，''））> @intMaxCount）

开始

选择@isValid ='N';

休息;

结束

选择@intIndex + = 1;

结束



返回@isValid

结束

ALTER FUNCTION [dbo].[CheckValidString]
(
@input varchar(50)

)
RETURNS varchar
AS
BEGIN

declare @inputLength integer = datalength(@input);
declare @intIndex integer = 1;
declare @intMaxCount integer = 3;
declare @singleChar Char(1);
declare @isValid Char(1);
while (1 = 1)
begin
if @intIndex > @inputLength
begin
select @isValid = 'Y';
break;
end

select @singleChar = substring(@input, @intIndex, 1)

if(@inputLength - datalength(replace(@input, @singleChar, '')) > @intMaxCount)
begin
select @isValid = 'N';
break;
end
select @intIndex += 1;
end

RETURN @isValid
END

这篇关于如何在sql查询中查找包含三个以上重复char ex：（Acabacaga）的输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！