Shafizadeh
Shafizadeh

Reputation: 10340

How to replace just last character?

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)

Upvotes: 1

Views: 151

Answers (3)

Ramin Darvishov
Ramin Darvishov

Reputation: 1039

remove last corresponding characters if exist

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

Upvotes: 2

Robert
Robert

Reputation: 2824

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)

Upvotes: 2

Ramin Darvishov
Ramin Darvishov

Reputation: 1039

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

Upvotes: 3

Related Questions