Consecutive sequence in SQL

ghz 8months ago ⋅ 109 views

enter image description hereI have a columns similar the one shown below;-

Col1
------------
x1
x2,x3
x1,y5
y3,x5,x6
y6,y10,y11
x7,y8
y6,y9
;

I want to filter out only the rows that have at least 2 consecutive ones irrespective of the prefix alphabet. Rows 2,4,5 and 6 should be returned as a result.

I am trying to use SQL from another platform where replace function doesn't work. I was trying to remove text, then place numbers in different columns and compare among them to find consecutive numbers - like COl2 - Col1 if is 1 then true.

Answers

To achieve this in SQL without using the REPLACE function, you can split the values in Col1 into individual elements and then compare adjacent elements to identify rows with at least 2 consecutive ones. However, depending on your SQL platform, the method to split strings may vary.

Here's a general approach using SQL:

WITH SplitRows AS (
  SELECT
    Col1,
    REGEXP_SUBSTR(Col1, '[^,]+', 1, LEVEL) AS Col2,
    LEVEL AS RowNum
  FROM
    (SELECT Col1 FROM YourTable)
  CONNECT BY
    REGEXP_SUBSTR(Col1, '[^,]+', 1, LEVEL) IS NOT NULL
),
ConsecutiveOnes AS (
  SELECT
    Col1,
    Col2,
    RowNum,
    LAG(Col2) OVER (PARTITION BY Col1 ORDER BY RowNum) AS PrevCol2,
    LEAD(Col2) OVER (PARTITION BY Col1 ORDER BY RowNum) AS NextCol2
  FROM
    SplitRows
)
SELECT DISTINCT Col1
FROM ConsecutiveOnes
WHERE (Col2 = '1' AND PrevCol2 = '1') OR (Col2 = '1' AND NextCol2 = '1');

This query:

  1. Splits the values in Col1 into individual elements (Col2) using a recursive query or a similar method suitable for your SQL platform.
  2. Uses window functions (LAG and LEAD) to compare adjacent elements (Col2) within each row.
  3. Filters the rows to select only those where at least 2 consecutive ones are found.

Make sure to replace YourTable with the actual name of your table. Also, adjust the syntax according to the specific SQL dialect you are using, as different SQL platforms may have different functions and syntax for string manipulation and window functions.

#sql