Postgresql – Postgres – how to find contents of one field within another field

castpattern matchingpostgresqlquery

How do I find where the contents of one field are within another field?

I have a database table with a auto-generated sequence number in one field, and another character based field which should contain a string which describes a path to a file. The path should be based on the sequence in the other field, sometimes it isn't and I want to identify these rows.

This is query I've been trying (among others) but it doesn't work, position() is obviously designed to find a hard-coded string within a field, not another field

SELECT recordId, path
FROM TABLEA
WHERE position(recordID IN path) = 0;

RecordId is defined as:

recordId integer not nulll default nextval('tablea_recordid_seq'::regclass)

And path is:

character varying(80)

I can't help feeling that the correct solution is probably very simple, but damned if I can find it!

Ok, this works …

SELECT recordId, path
FROM TABLEA
WHERE position(CAST(recordID AS CHAR) IN path) = 0;

Best Answer

The path should be based on the sequence in the other field, sometimes it isn't and I want to identify these rows

So the absence of any actual position is what you are looking for, which is simpler:

SELECT recordId, path
FROM   table_a
WHERE  path !~ recordId::text;

Note the cast to text (or varchar, all the same).

Do NOT cast to char like you have it in your added (broken) solution. char is a synonym of character (or bpchar) and defaults to character(1) (!), so effectively only takes the first digit of your number and produces many false positives. Related:

Best way to check for “empty or null value”

!~ is the operator for negated regular expressions

NOT LIKE would do the job, too:

...
WHERE path NOT LIKE ('%' || recordId::text || '%');

Related Solutions

Mysql – Add parent_id dynamically based on another field

CREATE TEMP TABLE v2
    SELECT * 
    FROM version;

UPDATE version v1 
SET parent_id = (
    SELECT id 
    FROM v2 
    WHERE v2.order < v1.order 
    ORDER BY v2.order DESC LIMIT 1
);

SQL Query – How to Select Distinct for One Column and Any in Another Column

The easiest query to write is for MySQL (with not strict ANSI settings). It uses the non-standard construction:

SELECT key, value
FROM tableX
GROUP BY key ;

In recent version (5.7 and 8.0+) where the strict settings and ONLY_FULL_GROUP_BY are the default, you can use the ANY_VALUE() function, added in 5.7:

SELECT key, ANY_VALUE(value) AS value
FROM tableX
GROUP BY key ;

For other DBMSs, that have window functions (like Postgres, SQL-Server, Oracle, DB2), you can use them like this. The advantage is that you can select other columns in the result as well (besides the key and value) :

SELECT key, value
FROM tableX
    ( SELECT key, value,
             ROW_NUMBER() OVER (PARTITION BY key 
                                ORDER BY whatever)     --- ORDER BY NULL
               AS rn                                   --- for example
      FROM tableX
    ) tmp 
WHERE rn = 1 ;

For older versions of the above and for any other DBMS, a general way that works almost everywhere. One disadvantage is that you cannot select other columns with this approach. Another is that aggregate functions like MIN() and MAX() do not work with some datatypes in some DBMSs (like bit, text, blobs):

SELECT key, MIN(value) AS value
FROM tableX
GROUP BY key ;

PostgreSQL has a special non-standard DISTINCT ON operator that can also be used. The optional ORDER BY is for selecting which row from every group should be selected:

SELECT DISTINCT ON (key) key, value
FROM tableX
-- ORDER BY key, <some_other_expressions> ;

Best Answer

Related Solutions

Mysql – Add parent_id dynamically based on another field

SQL Query – How to Select Distinct for One Column and Any in Another Column

Related Question