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.
Add a comment
|
2 Answers
How about:
SELECT <whatever>
FROM <your tables>
WHERE one_field <> the_other_field
AND position(the_other_field in one_field) = 1;
4 Comments
John Doe
Thanks, that's exactly what I needed. Is
1 the first character here?
dslh
Yes, 1 is the index of the first match. position() will return 0 if there is no match.
John Doe
Don't you know if there is a way to do that with LIKE? I'm not sure, because I saw only hardcoded patterns in LIKE before.
tobixen
LIKE can be used, yes ... it will be almost the same as my solution below, just replace '~' with 'LIKE' and concat a '%' at the end instead of the '^' to the start. There is one advantage with LIKE, it may utilize indexes ... but thinking twice, that probably only applies to hard coded strings.
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 || '%');
3 Comments
John Doe
Don't you think the meaning of the other answer's code is more obvious if to read these options at first?
tobixen
The regexp has benefits if you need to expand the logic later. Also, if the long strings are very long, you'll get performance problems with the first solution since it has to go through the full long string to search for the short string.
tobixen
Came to think, you'll get performance problems with both solutions if the tables are big. It may be that you'll be able to exploit indexes by using LIKE, but I'm not 100% sure about that.