We have;

I want to query positions in a list:

arg = [(-1;-2), (1;1)]

My expected:

CodePudding user response：

Use a multi-dimensional IN:

SELECT *
FROM   table_name
WHERE  (x,y) IN ((-1,-2), (1,1))

Or, if you are passing the arg as a string then use LIKE:

SELECT *
FROM   table_name
WHERE  TRANSLATE('[(-1;-2),(1;1)]', '[]', ',,') LIKE '%,(' || x || ';' || y || '),%'

Or, if the round brackets can be used as the term delimiter then it can be simpler:

SELECT *
FROM   table_name
WHERE  '[(-1;-2),(1;1)]' LIKE '%(' || x || ';' || y || ')%'

Which, for the sample data:

CREATE TABLE table_name (PositionId, X, Y) AS
SELECT 1, -1, -2 FROM DUAL UNION ALL
SELECT 2, -1, -1 FROM DUAL UNION ALL
SELECT 3,  1,  2 FROM DUAL UNION ALL
SELECT 4,  1,  1 FROM DUAL;

All output:

fiddle

CodePudding user response：

In case arg is a string, you may convert it's a list of numbers (x,y):

WITH data(positionid, x, y) AS (
    SELECT 1, -1, -2 FROM DUAL UNION ALL
    SELECT 2, -1, -1 FROM DUAL UNION ALL
    SELECT 3, 1, 2 FROM DUAL UNION ALL
    SELECT 4, 1, 1 FROM DUAL -- UNION ALL  
),
expr(s) AS (
    SELECT regexp_replace( '[(-1;-2), (1;1)]', '\[(.*)\]', '\1') FROM DUAL 
),
pairs(n, s) AS (
    SELECT LEVEL, TRIM(regexp_replace( TRIM(regexp_substr(s,'[^,] ',1,LEVEL)) , '\((.*)\)', '\1')) as s
    FROM expr 
    CONNECT BY regexp_substr(s,'[^,] ',1,LEVEL) IS NOT NULL
)
, lopairs(x,y) AS (
    SELECT x, y FROM (
        SELECT n, LEVEL AS c, TO_NUMBER(regexp_substr(s,'[^;] ',1,LEVEL)) AS v
            FROM pairs
            CONNECT BY regexp_substr(s,'[^;] ',1,LEVEL) IS NOT NULL AND PRIOR n = n AND PRIOR SYS_GUID() IS NOT NULL
    )
    PIVOT (
        MAX(v) FOR c IN (1 AS x, 2 AS y)
    )    
)
SELECT positionid, x, y FROM data
WHERE (x,y) IN (
    SELECT x,y FROM lopairs
)
;