LinuxQuestions.org
Visit the LQ Articles and Editorials section
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
 
LinkBack Search this Thread
Old 06-09-2013, 03:22 PM   #1
a.albreiki
LQ Newbie
 
Registered: Apr 2008
Posts: 19

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, 03:45 PM   #2
TB0ne
Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 13,803

Rep: Reputation: 2365Reputation: 2365Reputation: 2365Reputation: 2365Reputation: 2365Reputation: 2365Reputation: 2365Reputation: 2365Reputation: 2365Reputation: 2365Reputation: 2365
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, 10:52 PM   #3
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.5
Posts: 16,086

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

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

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, 08:29 AM   #5
TB0ne
Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 13,803

Rep: Reputation: 2365Reputation: 2365Reputation: 2365Reputation: 2365Reputation: 2365Reputation: 2365Reputation: 2365Reputation: 2365Reputation: 2365Reputation: 2365Reputation: 2365
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 08:42 AM.
 
Old 06-10-2013, 11:57 PM   #6
a.albreiki
LQ Newbie
 
Registered: Apr 2008
Posts: 19

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
Trackbacks are Off
Pingbacks are On
Refbacks are 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 11:02 AM
Sed Command - - -How to change part of the name of multiple files sahil.jammu Linux - Newbie 6 04-02-2009 10:57 PM
Making mysql part of linux-environment jakobverner Linux - General 6 10-15-2007 09:15 AM
Deleted questions part of change? ZiaTioN LQ Suggestions & Feedback 1 12-04-2005 10:18 PM
MYSQL on Slackware9.0 ./mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 13) Litn Linux - Software 2 09-02-2003 09:00 PM


All times are GMT -5. The time now is 04:57 AM.

Main Menu
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