LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - General
User Name
Password
Linux - General This Linux forum is for general Linux questions and discussion.
If it is Linux Related and doesn't seem to fit in any other forum then this is the place.

Notices



Reply
 
Search this Thread
Old 06-09-2013, 04:22 PM   #1
a.albreiki
LQ Newbie
 
Registered: Apr 2008
Posts: 24

Rep: Reputation: 0
Smile how to change part of Value in Mysql


Hi to all

I have created data base with tables, and one table column contains Date

I need to change all date for example 2011090112 to 2011090100

I have data for several month, how can change only the last 2 digits from 12 to 00 for all rows.

thanks in advance..
 
Old 06-09-2013, 04:45 PM   #2
TB0ne
Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 15,082

Rep: Reputation: 2714Reputation: 2714Reputation: 2714Reputation: 2714Reputation: 2714Reputation: 2714Reputation: 2714Reputation: 2714Reputation: 2714Reputation: 2714Reputation: 2714
Quote:
Originally Posted by a.albreiki View Post
Hi to all
I have created data base with tables, and one table column contains Date I need to change all date for example 2011090112 to 2011090100 I have data for several month, how can change only the last 2 digits from 12 to 00 for all rows.

thanks in advance..
Ok...did you try to look up a solution, or read the MySQL documentation on the update command? This can be found on the MySQL forums:
Code:
update table set field = REPLACE(field, 'string', 'anothervalue') WHERE field LIKE '%string%';
 
Old 06-09-2013, 11:52 PM   #3
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.6, Centos 5.10
Posts: 16,324

Rep: Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041
Darn ignore this post...

Last edited by chrism01; 06-09-2013 at 11:54 PM.
 
Old 06-10-2013, 06:01 AM   #4
a.albreiki
LQ Newbie
 
Registered: Apr 2008
Posts: 24

Original Poster
Rep: Reputation: 0
Quote:
Originally Posted by TB0ne View Post
Ok...did you try to look up a solution, or read the MySQL documentation on the update command? This can be found on the MySQL forums:
Code:
update table set field = REPLACE(field, 'string', 'anothervalue') WHERE field LIKE '%string%';
Thanks TB0ne

I tried but I need to change all Values in the column date from 12 to 00. not only one value.. please advice me
 
Old 06-10-2013, 09:29 AM   #5
TB0ne
Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 15,082

Rep: Reputation: 2714Reputation: 2714Reputation: 2714Reputation: 2714Reputation: 2714Reputation: 2714Reputation: 2714Reputation: 2714Reputation: 2714Reputation: 2714Reputation: 2714
Quote:
Originally Posted by a.albreiki View Post
Thanks TB0ne
I tried but I need to change all Values in the column date from 12 to 00. not only one value.. please advice me
I did 'advice you', and gave you the exact command you need to do what you asked; modify the command to fit what you want. If you need to replace strings in lots of records, you have two choices:
  • Dump the table to a file, do a search/replace on it, and rebuild the table
  • Use the command given to you in a small script, to loop through the table and perform the update
Again, read the documentation on MySQL:
http://dev.mysql.com/doc/refman/5.0/...nction_replace
http://dev.mysql.com/doc/refman/5.0/...unction_substr

There are other commands/syntax you can use...again, a small bit of research will turn up lots.

Last edited by TB0ne; 06-10-2013 at 09:42 AM.
 
Old 06-11-2013, 12:57 AM   #6
a.albreiki
LQ Newbie
 
Registered: Apr 2008
Posts: 24

Original Poster
Rep: Reputation: 0
Quote:
Originally Posted by TB0ne View Post
I did 'advice you', and gave you the exact command you need to do what you asked; modify the command to fit what you want. If you need to replace strings in lots of records, you have two choices:
  • Dump the table to a file, do a search/replace on it, and rebuild the table
  • Use the command given to you in a small script, to loop through the table and perform the update
Again, read the documentation on MySQL:
http://dev.mysql.com/doc/refman/5.0/...nction_replace
http://dev.mysql.com/doc/refman/5.0/...unction_substr

There are other commands/syntax you can use...again, a small bit of research will turn up lots.
Thanks a lot TBOne for advice...
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
trying to change part of text with sequence for filename Adol Linux - Newbie 6 01-13-2013 12:02 PM
Sed Command - - -How to change part of the name of multiple files sahil.jammu Linux - Newbie 6 04-02-2009 11:57 PM
Making mysql part of linux-environment jakobverner Linux - General 6 10-15-2007 10:15 AM
Deleted questions part of change? ZiaTioN LQ Suggestions & Feedback 1 12-04-2005 11:18 PM
MYSQL on Slackware9.0 ./mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 13) Litn Linux - Software 2 09-02-2003 10:00 PM


All times are GMT -5. The time now is 08:57 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration