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;
2
  • Try casting recordID to text. – Laurenz Albe Oct 4 '19 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 '19 at 16:44
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:

!~ 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, privacy policy and cookie policy

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