I have come across another gem of a problem (and a resulting solution) during my endeavors at work. Essentially an SQL statement was needed that had to take several values from a table and join (concatenate) them together into one large string for use in a drop down box joomla style. But of course the plot thickens…

One of the values needed was a float that could be positive or negative, now this is fine in the case of a negative as the - symbol is already in the table. But for consistency I wanted a + symbol to appear if a positive value was returned. So through use of the CONCAT_WS, CONCAT and IF functions I came up with this lovely query:

SELECT CONCAT_WS(' ', a, '(', IF (b > 0, CONCAT('+', b), b), ')') as text, name as value FROM `example` ORDER BY `a`

This query will return data in the form of:

"valueofa (+/-valueofb)"

CONCAT_WS is taking an empty space as the separator between the values and joining them into one string. You will also notice that any other (non-value) text can be added in apostrophes, in this case brackets. The IF is checking if the value of b is greater than 0 and appending a + to the front of b using the CONCAT function.

Another problem sorted :)