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:
  #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: 14 ozopol User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 53 m 34 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
Dev Shed Novice (500 - 999 posts)
 
Join Date: Oct 2005
Location: Beaverton OR
Posts: 922 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: 49426 Folding Title: Beginner FolderFolding Points: 49426 Folding Title: Beginner FolderFolding Points: 49426 Folding Title: Beginner Folder
Time spent in forums: 4 Weeks 14 h 38 m 9 sec
Reputation Power: 493
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
Click here for more information.
 
Join Date: Feb 2003
Location: Toronto Canada
Posts: 19,829 r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level) 
Time spent in forums: 2 Months 6 Days 9 h 50 m 18 sec
Reputation Power: 1640
is that pseudo-html you're storing there?
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

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: 14 ozopol User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 53 m 34 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
Click here for more information.
 
Join Date: Feb 2003
Location: Toronto Canada
Posts: 19,829 r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level) 
Time spent in forums: 2 Months 6 Days 9 h 50 m 18 sec
Reputation Power: 1640
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: 14 ozopol User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 53 m 34 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
Click here for more information.
 
Join Date: Feb 2003
Location: Toronto Canada
Posts: 19,829 r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level) 
Time spent in forums: 2 Months 6 Days 9 h 50 m 18 sec
Reputation Power: 1640
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 7th Plane (8000 - 8499 posts)
 
Join Date: Jun 2001
Location: Outside US
Posts: 8,257 pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level) 
Time spent in forums: 3 Months 2 Days 11 h 54 m 38 sec
Reputation Power: 524
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
Click here for more information.
 
Join Date: Feb 2003
Location: Toronto Canada
Posts: 19,829 r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level) 
Time spent in forums: 2 Months 6 Days 9 h 50 m 18 sec
Reputation Power: 1640
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 7th Plane (8000 - 8499 posts)
 
Join Date: Jun 2001
Location: Outside US
Posts: 8,257 pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level) 
Time spent in forums: 3 Months 2 Days 11 h 54 m 38 sec
Reputation Power: 524
Rollback?

Reply With Quote
  #11  
Old March 14th, 2008, 12:19 PM
r937's Avatar
r937 r937 is offline
SQL Consultant
Click here for more information.
 
Join Date: Feb 2003
Location: Toronto Canada
Posts: 19,829 r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level) 
Time spent in forums: 2 Months 6 Days 9 h 50 m 18 sec
Reputation Power: 1640
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 7th Plane (8000 - 8499 posts)
 
Join Date: Jun 2001
Location: Outside US
Posts: 8,257 pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level) 
Time spent in forums: 3 Months 2 Days 11 h 54 m 38 sec
Reputation Power: 524
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
Click here for more information.
 
Join Date: Feb 2003
Location: Toronto Canada
Posts: 19,829 r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level)r937 User rank is General 10th Grade (Above 100000 Reputation Level) 
Time spent in forums: 2 Months 6 Days 9 h 50 m 18 sec
Reputation Power: 1640
Quote:
Originally Posted by pabloj
... you should be doing this inside a transaction which could be committed or rolled back after checking the results.
dude, you might know that, but people new to databases don't

remember, we are talking almost always to web developers who are not seasoned DBAs

Q: i lost my data! halp!
A: did you take a backup?
Q: no
A: that's bad judgment
Q: oh? and where does good judgment come from?
A: experience
Q: and where does experience come from?
A: bad judgment

Comments on this post
Annie79 agrees: lol

Reply With Quote
  #14  
Old March 17th, 2008, 09:02 AM
ozopol ozopol is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Mar 2008
Posts: 14 ozopol User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 53 m 34 sec
Reputation Power: 0
your interesting points makes me curious!

Any of you guys can maybe provide some hints about
how to do update / delete inside a transaction?

- to check the result before commit of course

Reply With Quote
  #15  
Old March 17th, 2008, 09:11 AM
pabloj's Avatar
pabloj pabloj is offline
Modding: Oracle MsSQL Firebird
Dev Shed God 7th Plane (8000 - 8499 posts)
 
Join Date: Jun 2001
Location: Outside US
Posts: 8,257 pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level) 
Time spent in forums: 3 Months 2 Days 11 h 54 m 38 sec
Reputation Power: 524
See my blog

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesMySQL Help > How to regex replace part of a string?


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


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

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump



 Free IT White Papers!
 
How to Present Effectively Online
This white paper offers practical and actionable advice on the key steps that any presenter should consider as they plan and execute a Webinar or online meeting.

 
Open Source Security Myths
Open Source Software (OSS) is computer software whose source code is available to the general public with relaxed or non-existent intellectual property restrictions (or arrangement such as the public domain), and is usually developed with the input of many contributors.

 
Power and Cooling Capacity Management for Data Centers
This paper describes the principles for achieving power and cooling capacity management.

 
Scalable, Fault-Tolerant NAS for Oracle - The Next Generation
For several years NAS has been evolving as a storage alternative for Oracle databases, and for good reason: NAS is quite often the simplest, most cost-effective storage approach for Oracle. Learn about the benefits that HP's approach to scalable NAS brings to Oracle environments in this comprehensive white paper.

 
Understanding Web Application Security Challenges
This white paper discusses many common threats and preventive measures for Web application security, and explains what you can do to help protect your organization.

 

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2009 by Developer Shed. All rights reserved. DS Cluster 6 hosted by Hostway
Stay green...Green IT