problem in using CONCAT if the value is Null in mysql

Question

i used CONCAT to get my tags and topics but if the story has no tag or topic then result is empty

 WHERE 

    CONCAT( ' ', table_stories.tags, ' ' )
    LIKE CONCAT( '%', table_tags.tid, '%' ) 

    AND

    CONCAT( ' ', table_stories.associated, ' ' )
    LIKE CONCAT( '%', table_topics.topicid, '%' )

as you see , everything fine unless we have story that has no tag or associated

i used

  table_stories.tags IS NULL OR

but problem still exists and cant fetch stories with no tag or associated

how can i fix this

Steve Weet · Accepted Answer · 2010-08-13 06:49:38Z

3

I don't have mysql handy to test but COALESCE() is probably the function you are looking for.

This returns the first non-null argument (See Documentation)

So

CONCAT(' ' , COALESCE(table_stories.associated, ' '), ' ')

should do the job

answered Aug 13, 2010 at 6:49

Steve Weet

28.5k11 gold badges72 silver badges86 bronze badges

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

1 Comment

Steve Weet Over a year ago

@Mac. I didn't post full code as I do not have access to mysql to test it. But within your CONCAT methods wrap any columns that could be null with a call to COALESCE(column, ' '). This will ensure that if the value is null you get a space instead.

Collectives™ on Stack Overflow

problem in using CONCAT if the value is Null in mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related