https://community.cloudera.com/t5/Support-Questions/how-to-split-name-based-on-special-character-in-hive/m-p/242005#M203808 <P>You can use below sql to convert the content as required</P><PRE>0: jdbc:hive2://localhost&gt; select * from temptable; +---------------------+--+ | temptable.fullname | +---------------------+--+ | abc xxx! def ghi | | abc systems! | +---------------------+--+ 0: jdbc:hive2://localhost&gt; select a[0] as LAST, substr(a[1], 0, length(a[1])-1) as TITLE, a[2] as FIRST, a[3] as MID from ( 0: jdbc:hive2://localhost&gt; select case 0: jdbc:hive2://localhost&gt; when instr(fullname, "!" )=length(fullname) then array('', '!', substr(fullname, 0, length(fullname)-1), '') 0: jdbc:hive2://localhost&gt; else split(fullname, " ") end as a from temptable) t; +-------+--------+--------------+------+--+ | last | title | first | mid | +-------+--------+--------------+------+--+ | abc | xxx | def | ghi | | | | abc systems | | +-------+--------+--------------+------+--+</PRE><P>If my answer helped to resolve the issue, please accept. It might help others in the community.</P> Fri, 11 Jan 2019 23:55:37 GMT nramanaiah 2019-01-11T23:55:37Z