I have a SQL table gathering form results. I noticed after about 200 results had been gathered, that instead of the date being in the format of 2011-06-01, that it was in the format of 2011-6-01, skipping the leading zero. This is giving some data processing problems. Is there a way to update all the 2011-6-xx values to make then 2011-06-xx? Solutions in either PHP/MySQL or just MS-SQL statements are acceptable, as the data is collected on a webserver using a PHP/MySQL form, exported to CSV,开发者_如何转开发 and then imported into an MS-SQL database on site for data analysis.
UPDATE `table` SET `date_column` = REPLACE(`date_column`, '2011-6-', '2011-06-') WHERE `date_column` LIKE '2011-6-%';
UPDATE Table1
SET field1 = REPLACE(field1, '2011-6-', '2011-06-')
WHERE field1 LIKE '2011-6%'
The where will allow you to use an index and replace much faster than looking through all rows.
Also in strict
mode MySQL will not execute UPDATE statements without a where
clause.
You talk about MS-SQL and MySQL. If it's MS SQL, then the below should work. I don't know if MySQL uses REPLACE
or has an equivalent function.
UPDATE
Some_Table
SET
some_string = REPLACE(some_string, '2011-6-', '2011-06-')
WHERE
some_string LIKE '2011-6%'
精彩评论