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

hconnor · #1 (**permalink**) 06-03-07, 15:40

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.

r937 · #2 (**permalink**) 06-03-07, 16:18

Code:

UPDATE physician
   SET physician_MI = CONCAT(physician_MI,'.')
 WHERE physician_MI not like '%.%'

aschk · #3 (**permalink**) 06-04-07, 03:50

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}$';

r937 · #4 (**permalink**) 06-04-07, 06:07

where did it say the middle initial could have only 1 or 2 characters?

Internet Services

Web Development

Digital Marketing

Consumer Tech