CODEWITHSUNDEEP
postgresqlselectconditional-statements
John Doe
John Doe

Reputation: 9764

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.

Upvotes: 6

Views: 15979

Answers (2)

tobixen
tobixen

Reputation: 4073

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

Upvotes: 2

dslh
dslh

Reputation: 1835

How about:

SELECT <whatever>
  FROM <your tables>
 WHERE one_field <> the_other_field
   AND position(the_other_field in one_field) = 1;

See string functions and operators.

Upvotes: 3

Related Questions