Question 113348 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How do you add multiple decimal points to a column in SQL?

I have a column in my table that contains 10-digit hts codes (0000.00.0000). Some of the values do not have the full stop points (0000000000). How can I add the full stop points to all the rows that do not have them?

Edit
The column type is VARCHAR
I want to update all rows where full stop is not present.

Tags:

2

Answers


  1. 0

    I would remove the full stops from all these columns using REPLACE() as part of the update, then you can apply some simple logic using a CONCAT() LEFT(), RIGHT() and SUBSTRING()
    to change the simple 0000000000 into 0000.00.0000 like this, rather than trying to identify only the columns without the dots

    UPDATE table 
    set column = CONCAT( 
                    LEFT(REPLACE(column, '.', ''),4), 
                    '.' , 
                    SUBSTRING(REPLACE(column, '.', ''),5,2), 
                    '.', 
                    RIGHT(REPLACE(column, '.', ''),4)
                );

    Test it using a select so you do no damage

    SELECT some_identifying_column, 
        CONCAT( 
                LEFT(REPLACE(column, '.', ''),4), 
                '.' , 
                SUBSTRING(REPLACE(column, '.', ''),5,2), 
                '.', 
                RIGHT(REPLACE(column, '.', ''),4)
            ) as justtesting;
    Login or Signup to reply.
  2. 0

    Another approach using insert comes to mind. As others already mentioned, it’s a good idea to remove the full stops before inserting them in the 5th and 8th position in the string

    select *, insert(insert(replace(hts,'.',''),5,0,'.'),8,0,'.')
from t;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search