How do you add multiple decimal points to a column in SQL?-CodePudding

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.

CodePudding user response：

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;

CodePudding user response：

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;