updating substrings in mysql

[kaptain kernel]

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?

[Pedro Graca]

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 =--

[kaptain kernel]

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