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)
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;