How to replace just last character?

Question

Here is my table:

// table
+----+--------+
| id | number |
+----+--------+
| 1  | 123    |
| 2  | 123.   |
| 3  | 12.3   |
+----+--------+

I want this:

// newtable
+----+--------+
| id | number |
+----+--------+
| 1  | 123    |
| 2  | 123    |
| 3  | 12.3   |
+----+--------+

How can I do that?

I can do that like this: (But I'm searching for faster and better approach)

// Not tested, But I think this works!
SELECT 
  id, 
  IF(RIGHT(number,1) == '.', REPLACE(number, '.', ''), number)
FROM table
// also I can use CAST(number as unsigned) instead of REPLACE(number, '.', '')

Well, is there any better solution? (without IF-statement)

Are you saying that a column called `number` is not stored using a numeric type? That seems like the first problem. — Gordon Linoff, Nov 20 '15 at 13:22
At first: What do you want to achieve? Do you perhaps want to replace the values ending with a dot character in the database? Do you just want to change the output of the database? — yunzen, Nov 20 '15 at 13:23
@GordonLinoff `number` has `int(11)` type. But I used something like this in my query `ROUND(number/1000,1)` and then `.` *(point)* will appear... Also I used `SUBSTR()` and then sometimes I get a `.` in the end of that number, Now I want to remove it. — Shafizadeh, Nov 20 '15 at 13:25
@HerrSerker What I'm trying to do is detecting a better approach for doing [this](http://stackoverflow.com/questions/33825323/how-to-show-big-numbers-per-k-stands-for-kilo/33826706#33826706). Actually I want to eliminate that middle `IF`-statement. — Shafizadeh, Nov 20 '15 at 13:27
Perhaps ypu should adress this in the output and not in the business logic? What language/framework do you query from? — yunzen, Nov 20 '15 at 13:37
@HerrSerker Honestly I didn't get your question, I used phpmyadmin for using of database and PHP for dynamic codes. — Shafizadeh, Nov 20 '15 at 13:41
@Shafizadeh Well, you certainly want to do something with the output from the database. But what you try to achieve is changing the output format of the database output. Maybe a database query is not the best place to do this. Format should be handled in the output itself (for example PHP) — yunzen, Nov 20 '15 at 13:50

score 3 · Answer 1 · answered Nov 20 '15 at 14:10

3

SELECT id, CONVERT(number, DECIMAL(10,6)) FROM test

answered Nov 20 '15 at 14:10

Ramin Darvishov

1,043
1
15
30

1

Can you please add some explain to your answer? What is `DECIMAL(10,6)` ? – Shafizadeh Nov 20 '15 at 14:17
`DECIMAL(10,6)` mean the number have 10 digits 4 of them before the fixed point and 6 after it – Robert Nov 20 '15 at 14:28
the output will be always like this 'X.yyyyyy' X can be 0 or higher but yyyyyy are always 6 , ex. '100.000000' for integer 100 or '0.666667' for 2/3 – Robert Nov 20 '15 at 14:34

Robert · Answer 2 · 2015-11-20T14:25:53.910

use this if you want to change the data in the table

mysql> CREATE TABLE test(
    -> id INT(1),
    -> number VARCHAR(5)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO test
    -> VALUES(1, '123'),
    -> (2, '123.'),
    -> (3, '12.3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test;
+------+--------+
| id   | number |
+------+--------+
|    1 | 123    |
|    2 | 123.   |
|    3 | 12.3   |
+------+--------+
3 rows in set (0.00 sec)

mysql> UPDATE test
    -> SET number=REPLACE(number, '.', '')
    -> WHERE RIGHT(number,1) = '.';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM test;
+------+--------+
| id   | number |
+------+--------+
|    1 | 123    |
|    2 | 123    |
|    3 | 12.3   |
+------+--------+
3 rows in set (0.00 sec)

and use this if you want just to get the data in this form

mysql> DROP TABLE IF EXISTS test;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE test(
    ->   id INT(1),
    ->   number VARCHAR(5)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO test VALUES
    ->   (1, '123'),
    ->   (2, '123.'),
    ->   (3, '12.3')
    -> ;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test;
+------+--------+
| id   | number |
+------+--------+
|    1 | 123    |
|    2 | 123.   |
|    3 | 12.3   |
+------+--------+
3 rows in set (0.00 sec)

mysql> SELECT
    ->   id,
    ->   CASE
    ->     WHEN RIGHT(number,1) = '.' THEN floor(number)
    ->     ELSE number
    ->   END AS number
    -> from test;
+------+--------+
| id   | number |
+------+--------+
|    1 | 123    |
|    2 | 123    |
|    3 | 12.3   |
+------+--------+
3 rows in set (0.00 sec)

Your solution is fine. But actually it is not what I wanted. +1 — Shafizadeh, Nov 20 '15 at 14:05
okay sorry i got it wrong first time , gonna try something else — Robert, Nov 20 '15 at 14:09

Ramin Darvishov · Accepted Answer · 2015-11-20T17:13:21.807

2

remove last corresponding characters if exist

SELECT id, TRIM(TRAILING '.' FROM number) FROM test

edited Nov 20 '15 at 17:13

answered Nov 20 '15 at 15:42

Ramin Darvishov

1,043
1
15
30

It's always a good thing to add an explanation for your code. Also, although it's not forbidden, it would be better if you provide the possible solutons in the same answer. Thanks! – Luís Cruz Nov 20 '15 at 16:46

How to replace just last character?

3 Answers3