Selecting values that begins with specified value located in the same or other table

Question

I want to select records that begin same as first but without it. First one could change depending on user's choice so solution must be 'dynamic'. I tried putting substring containing first three chracters of first record to NOT LIKE () function but it doesn't work, got messege that 'like (text) doesn't exist'. Could I get help to resolve this problem? Thanks in advance.

Edit: I'm using PostgreSQL 9.4.4 and I no longer get error, instead of this my query that is posted below returns entire table.

select * from codes where code not like substring((select code from codes where id = 1) from 1 for 3);

Table:
+--------+-----------+
|code_id |code       |
|[PK]int |varchar(10)|
+--------+-----------+
|1       |00011111   |
|2       |11111111   |
|3       |11122222   |
|4       |00022222   |
|5       |00033333   |
+--------+-----------+

Result:
+--------+-----------+
|code_id |code       |
|[PK]int |varchar(10)|
+--------+-----------+
|4       |00022222   |
|5       |00033333   |
+--------+-----------+

You forgot to provide your version on Postgres, the query you tried (even if it's not working) and the table definition showing data types, constraints, etc. (`\d tbl` in psql). Also, error messages should be copied *verbatim*. — Erwin Brandstetter, Jun 29 '15 at 13:20

titanofold · Answer 1 · 2015-07-01T20:29:07.727

[NOT] LIKE (which as a commenter stated is not written as like() because it isn't a function) is a bit different when it comes to matching. The percent symbol is your wild card and you need it to match the rest of the field.

SELECT * FROM table WHERE code LIKE '000%';

If you were to just do LIKE '000', that would be equivalent to code = '000', which wouldn't match anything. Or, conversely, NOT LIKE '000' would match everything.

To get what you're after involves something a bit messier to achieve the previous example by using concat() and substr():

SELECT *
  FROM codes
 WHERE code NOT LIKE concat(
    (SELECT substr(code::text, 1, 3) FROM codes WHERE id = 1), '%'
);

We use substr() in the sub-select to return just the first 3 characters in the code column up to concat(). Then we use concat() to generate a suitable string for NOT LIKE to use.

If the code column is already text-like, you can drop the ::text cast, but it wouldn't hurt anything to keep it there. substring() and substr() are effectively the same, but we can shorten up the query a bit with substr().

`LIKE` without wildcard wouldn't do the job, but it wouldn't produce an error, either. — Erwin Brandstetter, Jun 29 '15 at 13:22
I tried something like that before I posted here. It doesn't solve my problem because it won't work if first record would begin with 111. Question is how to write anything like 'dynamic regexp'. — Jesion, Jun 29 '15 at 16:14

score 1 · Accepted Answer · edited May 23 '17 at 12:33

Given this table definition:

CREATE TABLE codes (
  code_id int PRIMARY KEY
, code text);

This does the job:

SELECT c.*
FROM  (SELECT left(code, 3) || '%' AS pattern FROM codes WHERE code_id = 1) x
JOIN   codes c ON c.code LIKE x.pattern
WHERE  c.code_id <> 1;

SQL Fiddle.

Use left() (simpler, cheaper), LIKE - ~~NOT LIKE~~ would be backwards for (quote) records that begin same as first - and append the wildcard % to the pattern.

If your table is big (no use for small tables), an index will make this fast:

CREATE INDEX codes_text_pattern_ops_idx ON codes(code text_pattern_ops);

Details:

PostgreSQL LIKE query performance variations

score 0 · Answer 3 · answered Jul 01 '15 at 21:45

0

IMHO you don't need the like and the '%'

SELECT *
FROM codes c
WHERE c.id > 1
AND EXISTS (
  SELECT *
  FROM codes x
  WHERE LEFT(x.code, 3) = LEFT(c.code, 3)
  AND x.id = 1
  );

answered Jul 01 '15 at 21:45

wildplasser

43,142
8
66
109

Selecting values that begins with specified value located in the same or other table

3 Answers3