1

I have a php query working with no problems. However, prior to exporting to a csv file, I need to use a regular expressions to customize the look of a few variables. I have this working as well.

$form_field_number_UPD = preg_replace('(\W{3}.*)', '\n', $form_field_number);

My problem is that I need to export these customized variables, instead of the raw query results.

Is this possible?

Also,

I have been trying to use a regular expression in mysql 5.7 but have had no luck. Is there a preg_replace equivalent in MySQL 5.7? I haven't found one yet. There seems to be one in MySQL 7 but I cannot upgrade yet.

my php that I would like to find an mysql equivalent for:

$form_field_number_UPD = preg_replace('(\W{3}.*)', '\n', $form_field_number);

I tried below but it does not work.

REPLACE(fd.form_field_number, '(\W{3}.*)', '\n', 1, 5) AS New_Field

Thanks for any assistance, even if it is "look over here..."

Revised::

I have a checkbox group column where the results are:

result 1|~|result 1
result 2|~|result 2
result 3|~|result 3

The regex removes the |~| and everything to the right of it, so the results are:

result 1
result 2
result 3

I can do this in php but need to get these results into a downloadable csv file.

alexis
  • 48,685
  • 16
  • 101
  • 161
user1469
  • 111
  • 7
  • I am really bad in regex (preg_replace). so what are you trying to replace? – ratmalwer Nov 16 '18 at 20:04
  • I have a checkbox group column where the results are: result 1|~|result 1 result 2|~|result 2 result 3|~|result 3 The regex removes the |~| and everything to the right of it, so the results are: result 1 result 2 result 3 I can do this in php but need to get these results into a downloadable csv file. – user1469 Nov 16 '18 at 20:13

2 Answers2

1

I would suggest getting an array of the data you'd like to export into php using a MySQL SELECT query and using your php preg_replace() statement since you know that works. Exporting the replaced data to a csv file from the php array is pretty straightforward from there.

This link has answers with code samples of how this can be done: Export to CSV via PHP

GrantSlay
  • 109
  • 5
1

If your column is always demarcated with the symbol |~|, you don't need a regular expression; look for occurrences of this exact sequence, and use SUBSTR to chop off the rest of your string:

SELECT SUBSTR(fd.form_field_number, 1, LOCATE('|~|', fd.form_field_number)-1) AS trimmed
FROM ...
alexis
  • 48,685
  • 16
  • 101
  • 161
  • This would be perfect but I need to add a replace. My bad for not mentioning that the code I listed above is from a column in one row. So technically it looks like: result 1|~|result 1\nresult 2|~|result 2\nresult 3|~|result 3. so I would need to somehow combine the replace and substr. Is this possible? – user1469 Nov 16 '18 at 20:44
  • https://dev.mysql.com/doc/refman/5.7/en/string-functions.html. See if there's anything else you need there, while you are at it. – alexis Nov 16 '18 at 20:52
  • What kind of "data" is this, anyway? If this kind of material is in a single database cell, you are not really dealing with relational data and shouldn't try to do the whole job with SQL. Pull it into php, go to town with `regexp_replace()` and its friends, and write out the results with `fputcsv()`. – alexis Nov 16 '18 at 21:00