Deleting part of a string in MYSQL

Question

I want to delete part of a string found in a particular field.

For example, the entry in the field could be "01365320APS". The "APS" is what I am looking at deleting.

My question is, should I use:

SELECT SUBSTRING_INDEX('fieldname','APS', 1)

Needs more context - what about the strings with "APS1234945", or "1234APS45689"? — OMG Ponies, Jul 13 '11 at 16:04

score 110 · Accepted Answer · answered Jul 13 '11 at 16:05

110

When you want to edit a field, you need an UPDATE statement:

UPDATE table SET fieldname=REPLACE(fieldname,'APS','')

REPLACE is a string function that replaces every occurence of the 2nd string in the 1st string with the 3rd one.

Please try this with a WHERE clause first, to see if it is really what you want to do.

answered Jul 13 '11 at 16:05

Jacob

score 14 · Answer 2 · answered Jul 13 '11 at 16:04

14

For every occurrence of APS, try this:

UPDATE table SET column=REPLACE(column,'APS','');

answered Jul 13 '11 at 16:04

AJ.

2 Answers2