MySQL Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesMySQL Help

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!
  #1  
Old March 14th, 2008, 06:49 AM
ozopol ozopol is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Mar 2008
Posts: 12 ozopol User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 29 m 29 sec
Reputation Power: 0
How to regex replace part of a string?

Hi Forum

How to replace the beginings- and the ends of string without any change of the string in between?

An example:

this is my input:

col1
H1, verylongstring1... ,H
H2, verylongstring2... ,H

and this should be my output after replace:

col1
P1, verylongstring1... ,P11
P2, verylongstring2... ,P22
.

So far i can select col1 by:

SELECT col1 FROM table1 WHERE col1 REGEXP 'H1.*H';

but in :
UPDATE table1 SET col1 = REPLACE( col1, REGEXP 'H1.*H', REGEXP'P1.*P11');

the REGEXP does not work !?

thanks a lot for your help

Reply With Quote
  #2  
Old March 14th, 2008, 07:51 AM
Annie79's Avatar
Annie79 Annie79 is offline
Meow Black Belt
Click here for more information
 
Join Date: Oct 2005
Location: Beaverton OR
Posts: 913 Annie79 User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Annie79 User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Annie79 User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Annie79 User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Annie79 User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Annie79 User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Annie79 User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Annie79 User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Annie79 User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Annie79 User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)Annie79 User rank is Lieutenant Colonel (40000 - 50000 Reputation Level)  Folding Points: 47997 Folding Title: Beginner FolderFolding Points: 47997 Folding Title: Beginner FolderFolding Points: 47997 Folding Title: Beginner Folder
Time spent in forums: 4 Weeks 7 h 50 m 59 sec
Reputation Power: 433
You cannot combine regexp and replace() since matched parts cannot be captured.
http://forums.mysql.com/read.php?20,54128,54199
__________________

Reply With Quote
  #3  
Old March 14th, 2008, 08:00 AM
r937's Avatar
r937 r937 is offline
SQL Consultant
Dev Shed God 25th Plane (17000 - 17499 posts)
 
Join Date: Feb 2003
Location: Toronto Canada
Posts: 17,344 r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level) 
Time spent in forums: 1 Month 3 Weeks 5 Days 8 h 27 m 14 sec
Reputation Power: 891
is that pseudo-html you're storing there?
__________________
r937.com | rudy.ca

Reply With Quote
  #4  
Old March 14th, 2008, 08:17 AM
ozopol ozopol is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Mar 2008
Posts: 12 ozopol User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 29 m 29 sec
Reputation Power: 0
yes, you could say that.
actually the beginning and the ends of the strings are some tags that i want to search and replace , while i keep the inside content intact.

Reply With Quote
  #5  
Old March 14th, 2008, 08:19 AM
r937's Avatar
r937 r937 is offline
SQL Consultant
Dev Shed God 25th Plane (17000 - 17499 posts)
 
Join Date: Feb 2003
Location: Toronto Canada
Posts: 17,344 r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level) 
Time spent in forums: 1 Month 3 Weeks 5 Days 8 h 27 m 14 sec
Reputation Power: 891
SUBSTRING_INDEX with 1 and then -1 will allow you to find the first and last bits

if you only have the tags at the front and back, why do you store them at all?

Reply With Quote
  #6  
Old March 14th, 2008, 08:58 AM
ozopol ozopol is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Mar 2008
Posts: 12 ozopol User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 29 m 29 sec
Reputation Power: 0
Thank you,
What do you mean by 'why do you store them at all?' Are there any smarter ways to search and replace tags in the beginning and the end of a string?

SUBSTRING_INDEX will probably work in combination with REPLACE to do my search and replace. I was not aware of it. But I have not tried it yet.

Meanwhile I managed to do the job with two UPDATE queries.
The first one finds 'H1' and replaces it with 'P1'
and the second one finds 'H' and replaces it with 'P11' if in the beginning there is a 'P1'.

UPDATE table1 SET = col1 REPLACE( col1, 'H1', 'P1') WHERE col1 REGEXP 'H1.*H';
UPDATE table1 SET = col1 REPLACE( col1, 'H', 'P11') WHERE col1 REGEXP 'P1.*P11';

It could be nice if I could merge those two UPDATE queries into one and still do the same job !

Reply With Quote
  #7  
Old March 14th, 2008, 10:01 AM
r937's Avatar
r937 r937 is offline
SQL Consultant
Dev Shed God 25th Plane (17000 - 17499 posts)
 
Join Date: Feb 2003
Location: Toronto Canada
Posts: 17,344 r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level) 
Time spent in forums: 1 Month 3 Weeks 5 Days 8 h 27 m 14 sec
Reputation Power: 891
have a close look at what you are actually saying here --

... REPLACE( col1, 'H', 'P11')

i hope you made a backup


Reply With Quote
  #8  
Old March 14th, 2008, 10:29 AM
pabloj's Avatar
pabloj pabloj is offline
Modding: Oracle MsSQL Firebird
Dev Shed God 6th Plane (7500 - 7999 posts)
 
Join Date: Jun 2001
Location: Outside US
Posts: 7,711 pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level) 
Time spent in forums: 2 Months 2 Weeks 6 Days 5 h 22 m 58 sec
Reputation Power: 259
Quote:
... i hope you made a backup
Until mysql starts to run as a normal database out of the box (in this case I mean with autocommit set to OFF)

Reply With Quote
  #9  
Old March 14th, 2008, 10:42 AM
r937's Avatar
r937 r937 is offline
SQL Consultant
Dev Shed God 25th Plane (17000 - 17499 posts)
 
Join Date: Feb 2003
Location: Toronto Canada
Posts: 17,344 r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level) 
Time spent in forums: 1 Month 3 Weeks 5 Days 8 h 27 m 14 sec
Reputation Power: 891
and how would autocommit help someone get back all the Hs that were replaced other than the ones he actually meant to change?

before: H1,i hope you made a backup,H

after: P11,i P11ope you made a backup,P11

Reply With Quote
  #10  
Old March 14th, 2008, 11:17 AM
pabloj's Avatar
pabloj pabloj is offline
Modding: Oracle MsSQL Firebird
Dev Shed God 6th Plane (7500 - 7999 posts)
 
Join Date: Jun 2001
Location: Outside US
Posts: 7,711 pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level) 
Time spent in forums: 2 Months 2 Weeks 6 Days 5 h 22 m 58 sec
Reputation Power: 259
Rollback?

Reply With Quote
  #11  
Old March 14th, 2008, 12:19 PM
r937's Avatar
r937 r937 is offline
SQL Consultant
Dev Shed God 25th Plane (17000 - 17499 posts)
 
Join Date: Feb 2003
Location: Toronto Canada
Posts: 17,344 r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level) 
Time spent in forums: 1 Month 3 Weeks 5 Days 8 h 27 m 14 sec
Reputation Power: 891
and if you're on a hosted shared server?

Reply With Quote
  #12  
Old March 14th, 2008, 12:37 PM
pabloj's Avatar
pabloj pabloj is offline
Modding: Oracle MsSQL Firebird
Dev Shed God 6th Plane (7500 - 7999 posts)
 
Join Date: Jun 2001
Location: Outside US
Posts: 7,711 pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level) 
Time spent in forums: 2 Months 2 Weeks 6 Days 5 h 22 m 58 sec
Reputation Power: 259
I don't see the difference.
I mean that you should be doing this inside a transaction which could be committed or rolled back after checking the results.
Every (other) database works this way, but I think that mysql allows to set autocommit off for your session even if others are running with autocommit on (not shure).


Also, a restore on a hosted shared server would be more difficult than doing the same on your own private server.

Reply With Quote
  #13  
Old March 14th, 2008, 01:59 PM
r937's Avatar
r937 r937 is offline
SQL Consultant
Dev Shed God 25th Plane (17000 - 17499 posts)
 
Join Date: Feb 2003
Location: Toronto Canada
Posts: 17,344 r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level) 
Time spent in forums: 1 Month 3 Weeks 5 Days 8 h 27 m 14 sec
Reputation Power: 891
Quote: