I have a table with column name "fullname" which I would like to split into four columns (LAST_NAME, TITLE, FIRST_NAME, MIDDLE_NAME) while loading into another table.
If it is a person's name, the convention is LAST_NAME TITLE! FIRST_NAME MIDDLE_NAME:
for example I have "abc xxx! def ghi" in my table. This should be split and loaded in to 4 different columns.
the name should be split into LAST, FIRST, MID. Word Preceding the exclamation is TITLE (like xxx here).
If it is an organisation name ("Names of organisations are ended by an exclamation mark) : I should move the entire string to FIRST_NAME.
for example: abc systems! should be loaded to FIRST_NAME.
|abc xxx! def ghi|
Can someone help how to write a query for the above requirement? Thanks in Advance!