By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

updating substrings in mysql

P: n/a
how does one go about updating a substring in mysql
e.g.

I want to change all occurences of Gmbh to GMBH within a field string.

BMW gmbh
BMW Gmbh
Mercedes Gmbh
Joe Blogg Gmbh & Mercedes Ltd

get changed to

BMW GMBH
BMW GMBH
Mercedes GMBH
Joe Blogg GMBH & Mercedes Ltd

I know how to do this in PHP, but is there a way of doing it in MySQL
natively?

Jul 17 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
kaptain kernel wrote:
I want to change all occurences of Gmbh to GMBH within a field string.


Maybe like this:
-- SQL --
create table `test`.`xx` (
`id` int (2) UNSIGNED NOT NULL AUTO_INCREMENT ,
`description` varchar (32) NULL ,
PRIMARY KEY ( `id` )
)
insert into `test`.`xx` (description) values
('BMW gmbh'), ('BMW Gmbh'), ('Mercedes Gmbh'), ('Joe Blogg Gmbh &
Mercedes Ltd')
select * from xx
update xx
set description = INSERT(description, INSTR(description, 'gmbh'), 4,
'GMBH')
where LOCATE('gmbh', description)>0
select * from xx
-- END SQL --

HTH
--
--= my mail box only accepts =--
--= Content-Type: text/plain =--
--= Size below 10001 bytes =--
Jul 17 '05 #2

P: n/a
That is most excellent - thanks. i wasn't aware of the LOCATE function.
This will save a LOT of time (and PHP processing...)

Pedro Graca wrote:
kaptain kernel wrote:
I want to change all occurences of Gmbh to GMBH within a field string.


Maybe like this:
-- SQL --
create table `test`.`xx` (
`id` int (2) UNSIGNED NOT NULL AUTO_INCREMENT ,
`description` varchar (32) NULL ,
PRIMARY KEY ( `id` )
)
insert into `test`.`xx` (description) values
('BMW gmbh'), ('BMW Gmbh'), ('Mercedes Gmbh'), ('Joe Blogg Gmbh &
Mercedes Ltd')
select * from xx
update xx
set description = INSERT(description, INSTR(description, 'gmbh'), 4,
'GMBH')
where LOCATE('gmbh', description)>0
select * from xx
-- END SQL --

HTH


Jul 17 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.