Report Builder find carriage return and replace with ","

By Daryl - August 5, 2016

Hi Zick,

You need to call the REPLACE function as you select the column, ie:

SELECT
    num as 'Num', 
    REPLACE(results,\r\n,",") as 'Results' 
FROM `<?php echo $TABLE_PREFIX ?>form_submissions`
WHERE trueLIMIT 1000

Please let me know it that works for you.

Thanks!

Daryl Maximo
PHP Programmer - interactivetools.com

By Zicky - August 5, 2016

Hey Daryl,

I think I understand where you're going. I gave the solution you provided a try and I get the following message:

MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\r\n,",") as 'Results' FROM `cms_form_submissions` WHERE true LIMIT 1000' at line 3

I really don't know MySQL that well, so anything I had written is a complete stab in the dark and is based on suggestions I've found on the forum. So it's very likely what I had in the original post for the MySQL had issues to start with.

Thanks, Zick

By Zicky - August 5, 2016

Hey Daryl,

I was able to eliminate the syntax error with a slight change. by wrapping the \r\n in quotes '\r\n'

SELECT
    num as 'Num', 
    REPLACE(results,'\r\n',',') as 'Results' 
FROM `<?php echo $TABLE_PREFIX ?>form_submissions`
WHERE true
LIMIT 1000

and I got closer to the desired results of splitting the entire "Results" into columns by dropping \r and using '\n', '","'  

SELECT
    num as 'Num', 
    REPLACE(results, '\n''","') as 'Results' 
FROM `<?php echo $TABLE_PREFIX ?>form_submissions`
WHERE true
LIMIT 1000

The results I get isn't finished but it's a step closer.

Zick

By Daryl - August 8, 2016

Ahh yes, sorry about the typo. 

Please let me know if you have any questions.

Thanks!

Daryl Maximo
PHP Programmer - interactivetools.com