Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2007
    Posts
    34

    Unanswered: Adding periods after MI in db that has some MI and periods to start

    I'm trying to clean up a database that has middle initials in a field MI, but only a third of the records have a period after the MI.

    I thought of going through letter by letter and doing something like this
    UPDATE physician
    SET physician_MI=REPLACE(physician_MI,'A','A.');

    I tried that and if the letter has a period it ignores it and adds a period, so I have to do two steps, going back and replacing the two period letters. Not the end of the world, just checking to see if anyone has a better way of doing it maybe with a loop of the alphabet. (or two loops based on my results).

    Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    UPDATE physician
       SET physician_MI = CONCAT(physician_MI,'.')
     WHERE physician_MI not like '%.%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    I'm sure it's possible to use MySQL's regexp function also
    e.g.
    Code:
    UPDATE physician
       SET physician_MI = CONCAT(physician_MI,'.')
     WHERE physician_MI REGEXP '^[a-zA-Z]{1,2}$';

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    where did it say the middle initial could have only 1 or 2 characters?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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