LIKE with % on column names

postgresql sql-like
user1806801 picture user1806801 · Nov 7, 2012 · Viewed 28.2k times · Source

Here is my query that results in a syntax error:

SELECT * 
FROM account_invoice,sale_order
WHERE sale_order.name LIKE %account_invoice.origin%

The account_invoice.origin field contains the text of sale_order.name, plus other text as well, so I need to match sale_order.name string anywhere in the account_invoice.origin string.

I'm using PostgreSQL 8.4.

Answer

Marc picture Marc · Nov 7, 2012

Try this

SELECT * 
FROM account_invoice,sale_order
WHERE sale_order.name LIKE '%'  || account_invoice.origin || '%'

% needs single quote because the pattern is a string.

|| is the operator for concatenation.

Related questions

PostgreSQL LIKE query performance variations

I have been seeing quite a large variation in response times regarding LIKE queries to a particular table in my database. Sometimes I will get results within 200-400 ms (very acceptable) but other times it might take as much as 30 …

Read More
PostgreSQL: Select data with a like on timestamp field

I am trying to select data from a table, using a "like" on date field "date_checked" (timestamp). But I have this error : SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: timestamp without time zone My request is : SELECT my_…

Read More
How can you find a literal percent sign (%) in PostgreSQL using a LIKE query?

I have character varying entries in a table where some (not all) values contain percentages, e.g., '12%', '97%', etc. I want to find all the values that contain percentages. In other words, I want to find all …

Read More