MySQL Update Text field using TRIM(TRAILING

Question

I am trying to remove a carriage return in a MySQL Text type data field using the Update command. I've tried the following and when I export a record and view in a text editor (image attached) I still see this character? What should I use to remove this?

update fort_property_res SET property_information = TRIM(TRAILING '\n' FROM property_information 
update fort_property_res SET property_information = TRIM(TRAILING '\r' FROM property_information
update fort_property_res SET property_information = TRIM(TRAILING '\t' FROM property_information

What I see in a text editor

Did you check this post: stackoverflow.com/questions/17044316/… — cyadvert
– cyadvert, Commented Feb 12, 2014 at 19:45
Might have to to with the order of things... What does TRIM(TRAILING '\r\n\t' FROM property_information ) do? — Wrikken
– Wrikken, Commented Feb 12, 2014 at 19:48
@Wrikken its the last part of the SQL statement, I've updated the question with complete statement — Rocco The Taco
– Rocco The Taco, Commented Feb 12, 2014 at 19:51
@user3302748 considering there is not accepted answer and I'm already using TRIM I did not reference it. — Rocco The Taco
– Rocco The Taco, Commented Feb 12, 2014 at 19:52
@RoccoTheTaco: yes, what I'm saying it if you do an update per character, you could have leftovers: consider that a string ending with ...\t\r\n\t\r\n will be ...\t\r\n if you do it separately, but without them if you do it in one go with a characters list. Also, it may be some other whitespace: what does a HEX() of that field end in? — Wrikken
– Wrikken, Commented Feb 12, 2014 at 20:01

Ray Kiddy · Accepted Answer · 2014-02-12 19:52:14Z

1

Harder than it seems as though it should be, yes? Here is a way that works. Perhaps not the best, but it can get you started.

I tried something using rlike and it did not work with my first try. It seems that it should have though. O well.

mysql> create table foo (pk int primary key, name varchar(8));
Query OK, 0 rows affected (0.62 sec)

mysql> insert into foo values (1, 'aaa\t');
Query OK, 1 row affected (0.18 sec)

mysql> select * from foo;
+----+------+
| pk | name |
+----+------+
|  1 | aaa   |
+----+------+
1 row in set (0.00 sec)

mysql> update foo set name = substr(name,1,length(name)-1) where hex(name) like '%09';
Query OK, 1 row affected (0.23 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from foo;
+----+------+
| pk | name |
+----+------+
|  1 | aaa  |
+----+------+
1 row in set (0.00 sec)

answered Feb 12, 2014 at 19:52

Ray Kiddy

3,6163 gold badges27 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rocco The Taco Over a year ago

This has promise but did not work...perhaps because the line break has more text after this?

Rocco The Taco Over a year ago

...how do I determine the hex value?

Collectives™ on Stack Overflow

MySQL Update Text field using TRIM(TRAILING

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related