Results 1 to 5 of 5

Thread: Parsing a field

  1. #1
    Join Date
    Jan 2009
    Posts
    124

    Unanswered: Parsing a field

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use an application language like php

    sql would be too messy

    you're only going to do this once, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Posts
    124
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by oldnickj View Post
    Now I'd like to change the dates which are text to date
    STR_TO_DATE function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2009
    Posts
    124
    So eloquent!

    Ta

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •