Postgres - how to find contents of one field within another field 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;
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 || '%');