Compare text values in PostgreSQL
Say, there are two tables. One contains a field with long text values (for example foobarbaz), the other one contains shorter values (foobar and someothertext). I'd like to retrieve values from two tables with the following condition: the text must not be equal, but the beginning of the long string must match the short string. Is there a (neat) way to do this in Postgres? Thanks in advance.
SELECT <whatever> FROM <your tables> WHERE one_field <> the_other_field AND position(the_other_field in one_field) = 1;
As the other answer says, "position" can be used ... but I'd use regexp.
postgres=> create database test; CREATE DATABASE postgres=> \c test You are now connected to database "test". test=> create table long (long varchar); CREATE TABLE test=> create table short (short varchar); CREATE TABLE test=> insert into long values ('foobarbaz'); INSERT 0 1 test=> insert into long values ('qfoobarbaz'); INSERT 0 1 test=> insert into long values ('now this is a long text'); INSERT 0 1 test=> insert into short values ('foobar'); INSERT 0 1 test=> insert into short values ('someothertext'); INSERT 0 1 test=> select long.long from long join short on long.long <> short.short and long.long ~ ('^' || short.short); long ----------- foobarbaz (1 row)
caveat, short probably has to be escaped in case it contains regexp stuff.
(post-edit) - this is how it would look like when using LIKE (not tested):
select long.long from long join short on long.long <> short.short and long.long LIKE (short.short || '%');