Using MySQL Concat() in Where Clause

Using mysql concat() in WHERE clause?

What you have should work but can be reduced to:

select * from table where concat_ws(' ',first_name,last_name) 
like '%$search_term%';

Can you provide an example name and search term where this doesn't work?

MySQL : Concat in where clause not working

Following queries should work

select * from postal_address where concat(address1, ' ', address2) NOT LIKE 'ABC DEF';

select * from postal_address where concat(address1, ' ', address2) <> 'ABC DEF';

In MySQL, <> symbol refers to Not Equal to

Hope it helps!

Using CONCAT() AS column_name to WHERE clause

Fullname is an alias that you just gave to your "column". It does not exist in DB. If you want to search by "full name" in WHERE, you need to repeat the exercise there

WHERE CONCAT(students.FirstName, ' ', students.LastName) LIKE '%john%'

Optionally, you can pre-built inline view and then fullname will become a thing

SELECT * 
FROM
(
    SELECT
        CONCAT(students.FirstName, ' ', students.LastName) AS FullName
    FROM
        `students`
) inlineView
WHERE 
    FullName LIKE '%joe%' 

SQL CONCAT String + Column in WHERE clause

If your syntax works, then it is likely you are using MySQL. In any case, the problem is that you need quotes around string constants. So try this:

UPDATE Foo join
       Bar
       on Foo.A = concat('Z', Bar.A)
    SET Foo.B = Bar.B;

You should always use single quotes for string and date constants, regardless of the database. That is the ANSI standard and it reduced the possibility of error.

mysql query LIKE condition against a concat

You cannot use a field computed in the SELECT clause in the WHERE clause, because most (if not all) RDBMS evaluate the WHERE clause before the SELECT.

You probably want :

SELECT 
    id, first_name, last_name, 
    CONCAT(first_name,' ',last_name) AS full_name
FROM mytable 
WHERE CONCAT(first_name,' ',last_name) LIKE '%scott%'

Which can also be expressed :

SELECT 
    id, first_name, last_name, 
    CONCAT(first_name,' ',last_name) AS full_name
FROM mytable 
WHERE first_name LIKE '%scott%' OR last_name like '%scott%'

concat select not use in where clause (mysql)

Try to use CONCAT in WHERE clause like:

SELECT SQL_CALC_FOUND_ROWS `id`, `user_id`, `payment_type`, 
`is_paid`, `is_invoiced`, `invoice_number`, `created_at`, `ip`,
`data`, `coupon_code`, `payment_total`, 
(select CONCAT(users.name,' ',users.surname) from users
WHERE CONCAT(users.name,' ',users.surname) LIKE '%M%'
and users.id = orders.user_id) as fullname
FROM orders
WHERE `payment_type` LIKE '%M%' ORDER BY `is_invoiced` asc

MySQL - CONCAT two fields and use them in WHERE clause

Try this ::

SELECT CONCAT_WS(' ', first_name, last_name) AS name FROM `users`
WHERE CONCAT_WS(' ', first_name, last_name) LIKE "%John Doe%"

---

Related Topics