https://community.cloudera.com/t5/Support-Questions/how-to-split-name-based-on-special-character-in-hive/m-p/242004#M203807 <P>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.</P><P> If it is a person's name, the convention is LAST_NAME TITLE! FIRST_NAME MIDDLE_NAME: </P><P> for example I have "abc xxx! def ghi" in my table. This should be split and loaded in to 4 different columns. </P><P>the name should be split into LAST, FIRST, MID. Word Preceding the exclamation is TITLE (like xxx here). </P><P>If it is an organisation name ("Names of organisations are ended by an exclamation mark) : I should move the entire string to FIRST_NAME. </P><P> for example: abc systems! should be loaded to FIRST_NAME. </P><P>current state:</P><TABLE><TBODY><TR><TD>fullname</TD></TR><TR><TD>abc xxx! def ghi</TD></TR><TR><TD>abc systems!</TD></TR></TBODY></TABLE><P>expected result:</P><TABLE><TBODY><TR><TD>Last</TD><TD>Title</TD><TD>First</TD><TD>Middle</TD></TR><TR><TD>abc</TD><TD>xxx</TD><TD>def</TD><TD>ghi</TD></TR><TR><TD></TD><TD></TD><TD>abc systems</TD><TD></TD></TR></TBODY></TABLE><P>Can someone help how to write a query for the above requirement? Thanks in Advance!</P> Fri, 11 Jan 2019 22:36:24 GMT madankumarpuril 2019-01-11T22:36:24Z