Please let me know if you have any questions.

Thanks!

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

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

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!

That can perhaps help you.
http://stackoverflow.com/questions/9362213/i-need-to-find-and-replace-n-in-a-mysql-field

Djulia

In the reports is a column called "results" which contain user details from forms they submit. Below is an example:

First Name: John Last Name: Doe Address: 123 Main St. Address Cont: Suite 100 # 22 City: New York State: NY Zipcode: 10000 Age Group: 30-39 Electronic Signature: John Doe I acknowledge and agree to the Terms of Use: 1

I need to convert the column "results" into individual columns using "," to separate the entries like so:

"First Name: John Last", "Name: Doe", "Address: 123 Main St.", "Address Cont: Suite 100 # 22", "City: New York", "State: NY", "Zipcode: 10000", "Age Group: 30-39", "Electronic Signature: John Doe", "I acknowledge and agree to the Terms of Use: 1"

Here is what I have created so far in Report Builder:

SELECT
    *
FROM `<?php echo $TABLE_PREFIX ?>form_submissions`
REPLACE(results,\r\n,",")
WHERE true
LIMIT 1000

But it doesn't work, and throws the following error:

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 'REPLACE(results,\r\n,",") WHERE true LIMIT 1000' at line 4

I've also attached a snapshot of the header row of the report showing all the other columns within this report.

Anyone have any suggestions, or can point me in the right direction to get this working?

Thanks, Zick