CODEWITHSUNDEEP
sql-server
Panda1122
Panda1122

Reputation: 121

Identify non matching record in the row - SQL Server

I have below data. I want to remove odd out of that, which is at position 62. Because all other are set (Where). Basically combination of open bracket, where string and closing bracket.

id  pos    pattern
--------------------
5   26     (
1   48     where
8   60     )
2   62     where
6   74     (
3   109    where
9   121    )
7   133    (
4   168    where
10  180    )

Please suggest.

Upvotes: 0

Views: 51

Answers (2)

bwakabats
bwakabats

Reputation: 703

I used sub-queries to select the TOP 1 before (ordered by pos desc) and TOP 1 after (ordered by pos asc) then checked they if they were not the correct values

SELECT *
FROM MyTable t
WHERE t.pattern='where'
AND(
    ISNULL((SELECT TOP 1 tPrevious.pattern FROM MyTable tPrevious WHERE tPrevious.pos<t.pos ORDER BY tPrevious.pos DESC),'') !='('
    OR ISNULL((SELECT TOP 1 tNext.pattern FROM MyTable tNext WHERE tNext.pos>t.pos ORDER BY tNext.pos),'') !=')'
)

Upvotes: 1

Charlieface
Charlieface

Reputation: 71578

You can use LEAD/LAG to get the previous and next rows.

SELECT *
FROM (
    SELECT *,
        prev = LAG(t.pattern) OVER (ORDER BY pos),
        nxt = LEAD(t.pattern) OVER (ORDER BY pos)
    FROM MyTable t
) t
WHERE t.pattern <> 'where' OR prev = '(' AND nxt = ')';

You can even delete via that derived table

DELETE t
FROM (
    SELECT *,
        prev = LAG(t.pattern) OVER (ORDER BY pos),
        nxt = LEAD(t.pattern) OVER (ORDER BY pos)
    FROM MyTable t
) t
WHERE t.pattern = 'where' AND (prev <> '(' OR nxt <> ')');

Upvotes: 2

Related Questions