CODEWITHSUNDEEP
sql
Spy
Spy

Reputation: 163

SQL statement like ? or

I have this sql statement 

SELECT * FROM customers WHERE first_name LIKE ?

It selects the customers from a search field by first name. I want to also search by last name but I cannot do it. I use this code and it doesn't work

SELECT * FROM customers WHERE (first_name LIKE ?) OR (last_name LIKE ?)

Any suggestions?

Upvotes: 0

Views: 67

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

Your second query:

SELECT *
FROM customers
WHERE (first_name LIKE ?) OR (last_name LIKE ?)

Should work -- but you need to pass the parameter in twice. You could get around this with named parameters:

SELECT *
FROM customers
WHERE (first_name LIKE :pattern) OR (last_name LIKE :pattern)

Or by using a subquery/CTE:

SELECT c.*
FROM customers c CROSS JOIN
     (SELECT ? as pattern) p
WHERE (c.first_name LIKE p.pattern) OR (c.last_name LIKE p.pattern);

Or, you could possible implement almost equivalent logic with a single expression:

SELECT *
FROM customers
WHERE first_name || ' ' || last_name LIKE '%' || ? || '%';

The latter isn't exactly equivalent, but it would be equivalent in many circumstances.

Upvotes: 1

Related Questions