Name extraction SQL formula problem
Posted on 2008-06-19
Need short term fix...
Need this formula to extract only the first name ignoring the middle initial and suffix.
The Patient Field lists the order of the names as such:
Doe, John M Jr
Doe, John M
The formula I have now hiccups on the first name extraction because of either the middle initial is NOT in the name or when the middle name has more than one word (eg: Doe, John Young Lee) I need just the first name EXCLUDING the middle initial (or full middle) in the output.
This is the existing formula:
First: Trim(Left(Trim(Replace([PATIENT],",","",InStr(1,[PATIENT],","))),InStr(1,Trim(Replace([PATIENT],",","",InStr(1,[PATIENT],",")))," ")-1))