51

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.

Aldwoni
  • 1,168
  • 10
  • 24
user1806801
  • 513
  • 1
  • 4
  • 4
  • looks like an openerp query? Shouldnt it be "sale_order.name = account_invoice.origin" to avoid matching "SO123" with "SO1234" – TimoSolo Jun 10 '14 at 07:57

1 Answers1

95

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.

Marc
  • 16,170
  • 20
  • 76
  • 119
  • 1
    Thanks, your solution solved my problem. It turns out that I also had my where clause backwards. It should have been: WHERE account_invoice.origin LIKE '%' || sale_order.name || '%' – user1806801 Nov 07 '12 at 19:33
  • 1
    why couldn't they(postgres) just use + operator? – SamuraiJack Apr 24 '18 at 06:18