CONCAT_WS returning first column before each subsequent column in results in MYSQL

Question

I have a table with a number of columns relating to medications and prescriptions. The columns are Drug, Dose, Route, Frequency. I have used CONCAT_WS and CONVERT(fields, USING UTF8) which has handle the BLOB very well, apart from a final touch. Code below.

SELECT 
CONVERT( CONCAT_WS(drug,
        ' | ',
        rout,
        ' | ',
        m.dose,
        ' | ',
        frequency) USING UTF8) AS 'Drug Info'

What i get returned is

| drug | rout | drug | dose | drug | frequency | drug

....and so on throughout the entire concatenated field.

Can anyone advise what i am doing wrong and how to return...

| drug | rout | dose | frequency

Its taken some research and trial and error for me to get this far with regards to learning CONCAT_WS and then CONVERT to manage the BLOB, but i am totally stuck here.

The idea is to use this code in a Crystal Report but without converting the BLOB it doesn't display. I just get a blank field.

I have found out that CR only accepts certain file formats as BLOB images so if anyone can tell me how to either find out what file format the BLOB is when created with MYSQL and change it And/or how to display it in Crystal Reports, it would be equally as useful and appreciated.

Thanks in advance!

Answer 1

The first argument in CONCAT_WS is the seperator:

so change to :

SELECT 
CONVERT( CONCAT_WS(' | ',
        drug,
        rout,
        m.dose,
        frequency) USING UTF8) AS 'Drug Info';