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.

Answers


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.


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

Need Your Help

How do I return a specific value if a string is not contained in an array

java arrays

I've written a program in which there are two arrays. One is an array of strings which contains the names of cities, and another contains the distances between each one. A method is run which retur...

Simple Regular Expression to return text from Wordpress title - qtranslate plugin

php regex wordpress-plugin qtranslate

I am using qtranslate wordpress plugin to store blog content in multiple languages. Now I need to extract content from qtranslate tags.