process text fields in mysql line by line

Demo:

mysql> create table mytable (id int primary key, t text );

mysql> insert into mytable values (1, 'path1\npath2\npath3');

mysql> select * from mytable;
+----+-------------------+
| id | t                 |
+----+-------------------+
|  1 | path1
path2
path3 |
+----+-------------------+
1 row in set (0.00 sec)

mysql> update mytable set t = concat('/', replace(t, '\n', '\n/'));

mysql> select * from mytable;
+----+----------------------+
| id | t                    |
+----+----------------------+
|  1 | /path1
/path2
/path3 |
+----+----------------------+

However, I would strongly recommend to store each path on its own row, so you don’t have to think about this. In SQL, each column should store one value per row, not a set of values.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top