SQL Server: select rows with multiple occurrences of a regular expression in a column

Question

Im rightly used to use MySQL, but not particularly familiar with SQL Server. Hard luck, the database that I come across here is on SQL Server 2014.

I have a table with a column whose values ​​are integers with leading, separating, and end semicolons, like these three dummy lines:

;905;1493;384;13387;29;933;467;28732;
;905;138;3084;1387;290;9353;4767;2732;
;9085;14493;3864;130387;289;933;4767;28732;

Now I am trying to select all rows in which this column displays more than one number taken from a list of numbers. So, for example, given the three lines above, if I have a group 905,467,4767, the Im statement, trying to figure out how to build, should return the first two lines: the first line contains 905 and 467; the second line contains 905 and 4767. The third line contains only 4767, so the line should not be returned.

As far as I can tell , SQL Server actually doesn't support regex (and I don't even know what managed code is), which doesn't help. Even with regex, I don’t know where to start. Oracle seems to have a feature that will be very useful , but it is Oracle.

( ) . , , , " 15 , SELECT . , (, , , 29, 29 , 290 ), , REPLACE, . .

, , - :

SELECT * FROM table WHERE REGEXP_COUNT(column, ';(905|467|4767);') > 1

- , , ( REGEXP_COUNT Oracle).

, ?

Answer 1

SELECT *
FROM   Mess
       CROSS APPLY (SELECT COUNT(*)
                    FROM   (VALUES (905),
                                   (467),
                                   (4767)) V(Num)
                    WHERE  Col LIKE CONCAT('%;', Num, ';%')) ca(count)
WHERE  count > 1 

SQL Fiddle

, ,

WITH Nums
     AS (SELECT Num
         FROM   (VALUES (905),
                        (467),
                        (4767)) V(Num))
SELECT Mess.*
FROM   Mess
       CROSS APPLY (VALUES(CAST(CONCAT('<x>', REPLACE(Col, ';', '</x><x>'), '</x>') AS XML))) x(x)
       CROSS APPLY (SELECT COUNT(*)
                    FROM   (SELECT n.value('.', 'int')
                            FROM   x.x.nodes('/x') n(n)
                            WHERE  n.value('.', 'varchar') <> ''
                            INTERSECT
                            SELECT Num
                            FROM   Nums) T(count)
                    HAVING COUNT(*) > 1) ca2(count) 

Answer 2

(, , ( , ) ) - ?

DECLARE @T table (String varchar(255))
INSERT INTO @T
VALUES
(';905;1493;384;13387;29;933;467;28732;')
, (';905;138;3084;1387;290;9353;4767;2732;')
, (';9085;14493;3864;130387;289;933;4767;28732;')

DECLARE @Arguments table (Arg int)
INSERT INTO @Arguments
VALUES
(905)
, (467)
, (4767)

SELECT String
FROM
    @T
    CROSS JOIN @Arguments
GROUP BY String
HAVING SUM(CASE WHEN PATINDEX('%;' + CAST(Arg AS varchar) + ';%', String) > 0 THEN 1 ELSE 0 END) > 1

:

CREATE FUNCTION GenerateArguments (@Integers varchar(255))
RETURNS @Arguments table (Arg int)
AS

BEGIN

    WITH cte
    AS
    (
        SELECT
            PATINDEX('%,%', @Integers) p
            , LEFT(@Integers, PATINDEX('%,%', @Integers) - 1) n
        UNION ALL
        SELECT
            CASE WHEN PATINDEX('%,%', SUBSTRING(@Integers, p + 1, LEN(@Integers))) + p = p THEN 0 ELSE PATINDEX('%,%', SUBSTRING(@Integers, p + 1, LEN(@Integers))) + p END
            , CASE WHEN PATINDEX('%,%', SUBSTRING(@Integers, p + 1, LEN(@Integers))) = 0 THEN RIGHT(@Integers, PATINDEX('%,%', REVERSE(@Integers)) - 1) ELSE LEFT(SUBSTRING(@Integers, p + 1, LEN(@Integers)), PATINDEX('%,%', SUBSTRING(@Integers, p + 1, LEN(@Integers))) - 1) END
        FROM cte
        WHERE p <> 0
    )

    INSERT INTO @Arguments (Arg)

    SELECT n
    FROM cte

    RETURN

END
GO

DECLARE @T table (String varchar(255))
INSERT INTO @T
VALUES
(';905;1493;384;13387;29;933;467;28732;')
, (';905;138;3084;1387;290;9353;4767;2732;')
, (';9085;14493;3864;130387;289;933;4767;28732;')
;

SELECT String
FROM
    @T
    CROSS JOIN GenerateArguments('905,467,4767')
GROUP BY String
HAVING SUM(CASE WHEN PATINDEX('%;' + CAST(Arg AS varchar) + ';%', String) > 0 THEN 1 ELSE 0 END) > 1

Answer 3

, row_number, .

:

DECLARE @tbl TABLE (
string NVARCHAR(MAX)
)

INSERT @tbl VALUES
(';905;1493;384;13387;29;933;467;28732;'),
(';905;138;3084;1387;290;9353;4767;2732;'),
(';9085;14493;3864;130387;289;933;4767;28732;')

Then we pass your search parameters to the table variable to which we need to add:

DECLARE @search_tbl TABLE (
search_value INT
)

INSERT @search_tbl VALUES
(905),
(467),
(4767)

Finally, we join the table with a column to search in the search table. We use the row_number function to determine the number of matches. We select from this subquery where row_number = 2 means that it connects at least twice.

SELECT
    string
FROM (
    SELECT
        tbl.string,
        ROW_NUMBER() OVER (PARTITION BY tbl.string ORDER BY tbl.string) AS rn
    FROM @tbl tbl
    JOIN @search_tbl search_tbl ON
        tbl.string LIKE '%;' + CAST(search_tbl.search_value AS NVARCHAR(MAX)) + ';%'
    ) tbl
WHERE rn = 2

Answer 4

You can create a where clause as follows:

WHERE
case when column like '%;905;%' then 1 else 0 end +
case when column like '%;467;%' then 1 else 0 end +
case when column like '%;4767;%' then 1 else 0 end >= 2

The advantage is that you do not need a helper table. I do not know how you create the query, but it also works and is useful if the numbers are in the tsql variable.

 case when column like ('%;' + @n + ';%')  then 1 else 0 end