查找内部包含重复字母的字符串 [英] Finding strings with duplicate letters inside

问题描述

有人可以帮我完成这个小任务吗?我需要的是一个存储过程，该存储过程可以从表"a"中的字符串中找到重复的字母(连续)，然后用仅具有重复字母的字符串的ID来创建新表"b"./p>

类似这样的东西:

表A

ID Name   
1  Matt
2  Daave
3  Toom
4  Mike
5  Eddie

从该表中，我可以看到Daave，Toom，Eddie连续有重复的字母，我想创建一个新表并仅列出其ID.像这样:

表B

ID     
2
3
5

仅2,3,5，因为这是名称中具有重复字母的字符串的ID.

我希望这是可以理解的，并且非常感谢您的帮助.

解决方案

在对存储过程的回答中，您有2个错误，一个错误是列名和LIKE子句之间缺少空格，第二个错误是搜索参数周围缺少单引号./p>

我首先创建用户定义的标量函数，如果字符串包含重复的字母，则返回1:

已编辑

CREATE FUNCTION FindDuplicateLetters
(
    @String NVARCHAR(50)
)
RETURNS BIT
AS
BEGIN

    DECLARE @Result BIT = 0 
    DECLARE @Counter INT = 1

    WHILE (@Counter <= LEN(@String) - 1) 
    BEGIN


    IF(ASCII((SELECT SUBSTRING(@String, @Counter, 1))) = ASCII((SELECT SUBSTRING(@String, @Counter + 1, 1))))
        BEGIN
             SET @Result = 1
             BREAK
        END


        SET @Counter = @Counter + 1 
    END

    RETURN @Result

END
GO

创建函数后，只需通过简单的SELECT查询调用它，如下所示:

SELECT 
    * 
FROM
    (SELECT 
        *, 
        dbo.FindDuplicateLetters(ColumnName) AS Duplicates
    FROM TableName) AS a
WHERE a.Duplicates = 1

通过这种组合，您将仅获得具有重复字母的行.

Can somebody help me with this little task? What I need is a stored procedure that can find duplicate letters (in a row) in a string from a table "a" and after that make a new table "b" with just the id of the string that has a duplicate letter.

Something like this:

Table A

ID Name   
1  Matt
2  Daave
3  Toom
4  Mike
5  Eddie

And from that table I can see that Daave, Toom, Eddie have duplicate letters in a row and I would like to make a new table and list their ID's only. Something like:

Table B

ID     
2
3
5

Only 2,3,5 because that is the ID of the string that has duplicate letters in their names.

I hope this is understandable and would be very grateful for any help.

解决方案

In your answer with stored procedure, you have 2 mistakes, one is missing space between column name and LIKE clause, second is missing single quotes around search parameter.

I first create user-defined scalar function which return 1 if string contains duplicate letters:

EDITED

CREATE FUNCTION FindDuplicateLetters
(
    @String NVARCHAR(50)
)
RETURNS BIT
AS
BEGIN

    DECLARE @Result BIT = 0 
    DECLARE @Counter INT = 1

    WHILE (@Counter <= LEN(@String) - 1) 
    BEGIN


    IF(ASCII((SELECT SUBSTRING(@String, @Counter, 1))) = ASCII((SELECT SUBSTRING(@String, @Counter + 1, 1))))
        BEGIN
             SET @Result = 1
             BREAK
        END


        SET @Counter = @Counter + 1 
    END

    RETURN @Result

END
GO

After function was created, just call it from simple SELECT query like following:

SELECT 
    * 
FROM
    (SELECT 
        *, 
        dbo.FindDuplicateLetters(ColumnName) AS Duplicates
    FROM TableName) AS a
WHERE a.Duplicates = 1

With this combination, you will get just rows that has duplicate letters.

这篇关于查找内部包含重复字母的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！