I need to parse a field which contains a data string such as:
Pd 1/14/11, ck# 898, $295 dues 2/1/10-11/30/11

into separate fields like: paidDate, checkNumber, amount, type, subDate, endDate.

if this were a select statement I could trim or substr. but what would an update look like?

Thanks Nick

use an application language like php

sql would be too messy

you're only going to do this once, right?

I was successful using the following statements:
UPDATE usersComment set pdDate =SUBSTR((SUBSTRING_INDEX((SUBSTRING_INDEX(staff_co mment_usr,',',2)),',',1)),4,8)

UPDATE usersComment set pdAmount=SUBSTR(SUBSTR(SUBSTRING_INDEX((SUBSTRING_ INDEX(staff_comment_usr,',',-1)),',',1),1,5),-3,5)

UPDATE usersComment set pdCheckNum =SUBSTRING_INDEX((SUBSTRING_INDEX(staff_comment_us r,',',2)),',',-1)

UPDATE usersComment set pdEffect=SUBSTR(SUBSTRING_INDEX(staff_comment_usr, '-',1),-7,8)

UPDATE usersComment set pdExpire=
SUBSTRING_INDEX((SUBSTRING_INDEX(staff_comment_usr ,'-',-1)),',',1)

Now I'd like to change the dates which are text to date format i.e.

The text is in this format mm/dd/yy (2/24/11) obviously just changing the field type doesn't work.

any ideas?

Nick

STR_TO_DATE function

So eloquent!

Ta