Clever Geek Handbook

Mysql removes the very last character

you might think there is an answer to this, but I cannot find the exact answer. I need to remove the last character in a string, it can be any character, and the string can be any length.

example 992899d should be 992899

+3


source to share


3 answers


Any solution using SUBSTRING only displays the contents of the field, removing the last character. This will not actually update the content of the column. If this is what you want (i.e. using it with SELECT ), SUBSTRING is sufficient .

But if you want to really update the column value, you can try this:

UPDATE <table_name>
SET <column_name> = CONCAT(LEFT(<column_name>, CHAR_LENGTH(<column_name>) -1), '')
WHERE <condition>;


This will replace the last character with nothing, so the last character will be removed.

Refer: http://dev.mysql.com/doc/refman/5.7/en/string-functions.html

+10


source


You may be looking for SUBSTRING(column, 1, CHAR_LENGTH(column)-1)

.



See http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring for reference.

+5


source


Use TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM ] str)

because you already know which section of the line you want to delete.

Here is a table with dates and a given amount that I pulled from my database:

select date_format(date, '%m/%d %I:%i%p') as Date ...
Date        | QTY
-------------------
3/5 11:30AM | 46
3/5 11:30PM | 23
3/6 11:30AM | 12
...

Using Trim:

select trim(trailing 'M' from date_format(date, '%m/%d %I:%i%p')) as Date ...
Date        | QTY
-------------------
3/5 11:30A  | 46
3/5 11:30P  | 23
3/6 11:30P  | 12
...

Confirm https://www.w3resource.com/mysql/string-functions/mysql-trim-function.php for helping me figure out the setup

0


source






More articles:


All Articles