First off I want to say that I know I'll come up with something on my own, I'm just looking for other suggestions and approaches to this requirement.
Here's the sample data and required results...
DECLARE @sampleData TABLE
SELECT 999888899, 'MIKE SMITH^^^^' UNION ALL
SELECT 123456789, 'SELBURG JASON L' UNION ALL
SELECT 377775014, 'GEORGE W TAKEYAMO' UNION ALL
SELECT 354517002, 'WAYNE A YADA^^^^' UNION ALL
SELECT 115455522, 'DONNA M AABERG^^^^' UNION ALL
SELECT 349555554, 'ECKERLIND^FRANCES^C^^' UNION ALL
SELECT 351874470, 'YABOVICH^LISA^M^^' UNION ALL
SELECT 351874470, 'JONES^STEVE^^^' UNION ALL
SELECT 354891162, 'YALUNG^JOSEFINA^Y^^'
Output should be in the format LASTNAME^FIRSTNAME^MIDDLEINITIAL^^
The carrot '^' splits each word
A total of four '^' symbols, so if no middle initial then LASTNAME^FIRSTNAME^^^
I understand that the biggest hurdle is determining whether or not a record
with no middle initial is FIRST-LAST or LAST-FIRST, but let's ignore that issue for now.
Assume that there will not be any single word names, everyone has at least a first and last name
Any suggestions are appreciated, and I'm specifically interested in the SET based approach. 😎
NOTE: This is a third part application and the fact that the data isn't normalized is beyond my control and CANT BE CHANGED. So please don't reply telling me that a correct design in the first place would avoid this problem... DUH! 😀
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.Jason L. Selburg