CONCAT() not returning requested string

Question

With this query, I want a name comprised of three fields to be returned. I don't understand why the concat() function is not returning all the requested values in the table. Some records will be filled--let `s say like 1-2-3. The result should then be "1 2 3". If the 3 records are 1-0-3 than the result should be "1 3." If the records are 1-2-0 then the result should be "1 2."

The three requested fields are all VARCHAR format.

The first and second records are wrong, the third and fourth are right, then, again, some are right and some are wrong.

SELECT pt_rooms.room_id, pt_rooms.room_hotel_id ,
a_room_type.room_type_name, 
pt_rooms.room_title, a_room_type_suffix.room_type_suffix_name ,    
CONCAT(a_room_type.room_type_name,' ', pt_rooms.room_title,' ',   
a_room_type_suffix.room_type_suffix_name ) 
AS long_name FROM `pt_rooms` 
LEFT JOIN a_room_type_suffix ON   
a_room_type_suffix.room_type_suffix_id=pt_rooms.room_type_suffix_id 
JOIN a_room_type ON a_room_type.room_type_id=pt_rooms.room_type;

txs phroureo SELECT pt_rooms.room_id, pt_rooms.room_hotel_id , a_room_type.room_type_name, pt_rooms.room_title, a_room_type_suffix.room_type_suffix_name , CONCAT_WS(' - ','a_room_type.room_type_name','pt_rooms.room_title','a_room_type_suffix.room_type_suffix_name') as long_name FROM pt_rooms LEFT JOIN a_room_type_suffix ON a_room_type_suffix.room_type_suffix_id=pt_rooms.room_type_suffix_id JOIN a_room_type ON a_room_type.room_type_id=pt_rooms.room_type; — papacico
– papacico, Commented Nov 17, 2015 at 21:44
i have modifyed the query as per concat_ws documentation but stil i get wrong results — papacico
– papacico, Commented Nov 17, 2015 at 21:45
hi, ok it is solve now i had to remove backthicks in concat_ws() string — papacico
– papacico, Commented Nov 18, 2015 at 11:54

gen_Eric · Accepted Answer · 2015-11-17 20:58:16Z

3

CONCAT() will return NULL if any of the values passed to it are NULL.

To get around this, you can use CONCAT_WS():

CONCAT_WS(' ', a_room_type.room_type_name, pt_rooms.room_title, a_room_type_suffix.room_type_suffix_name) AS long_name

answered Nov 17, 2015 at 20:58

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

CONCAT() not returning requested string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related