0

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;
3
  • Try casting recordID to text. Oct 4, 2019 at 14:18
  • It sounds like you just want to check if recordid is not present within the contents of path, have you tried something simple like this, select recordId, path from tablea where path not like '%' || recordId || '%';
    – kevinnwhat
    Oct 4, 2019 at 16:44
  • So is this question still open? Nov 13, 2021 at 0:21

1 Answer 1

1

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:

!~ is the operator for negated regular expressions

NOT LIKE would do the job, too:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.