开发者

Update a column in SQL, inserting a character at a specific point

开发者 https://www.devze.com 2023-03-14 22:33 出处:网络
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, skip

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%'
0

精彩评论

暂无评论...
验证码 换一张
取 消