How to Split single column value in to multiple using mysql

Question

string str2 = "select pm.paxseqno,pm.guestallowed,pm.paxname,pm.cardtype,pm.class,pm.paxseatno,pm.status,pm.ffpno,pm.Time,CASE WHEN cf.Cabin = 'F' THEN'First Class'WHEN cf.Cabin = 'E' THEN 'Economy' WHEN cf.Cabin = 'P' THEN 'Premiere'WHEN cf.Cabin = 'K' THEN 'Unknown'ELSE 'Unknown' END as Cabin from paxmaster Pm left join cabininfo cf on Pm.class=cf.class pm.paxname contails Full Name Like 'DALAL MIHIRMR' this and i want to split name in to two column Like First Name is' MIHIRMR' and Second Clolumn is 'DALAL' in My Sql So How to do this Please Help

Jignesh Kumar · Answer

Please try below query, string str2 = "select pm.paxseqno,pm.guestallowed,left(pm.paxname, CHARINDEX(' ', pm.paxname)) as firstName,substring(pm.paxname, CHARINDEX(' ', pm.paxname)+1, len(pm.paxname)-(CHARINDEX(' ', pm.paxname)-1)) as lastName,pm.cardtype,pm.class,pm.paxseatno,pm.status,pm.ffpno,pm.Time,CASE WHEN cf.Cabin = 'F' THEN'First Class'WHEN cf.Cabin = 'E' THEN 'Economy' WHEN cf.Cabin = 'P' THEN 'Premiere'WHEN cf.Cabin = 'K' THEN 'Unknown'ELSE 'Unknown' END as Cabin from paxmaster Pm left join cabininfo cf on Pm.class=cf.class

Faisal Pathan · Answer

SELECT SUBSTRING_INDEX(trim(title),' ',-1) last_name, SUBSTRING_INDEX(trim(title),' ',1) first_name from Test

Mukesh · Answer

will you please write full query

Faisal Pathan · Answer

try with SUBSTRING_INDEX(POS, ',', 1) for position1 SUBSTRING_INDEX(POS, ',', -1) for position2

Mukesh · Answer

getting error in my code- select left(paxname, CHARINDEX(' ', paxname)) as firstname, substring(paxname, CHARINDEX(' ', paxname)+1, len(paxname)-(CHARINDEX(' ', paxname)-1)) as lastname from paxmaster error is - Function Loungedb.CHARINDEX does not exist

Faisal Pathan · Answer

try this select left ( name , CHARINDEX( ' ' , name )) as firstname, substring ( name , CHARINDEX( ' ' , name )+1, len( name )-(CHARINDEX( ' ' , name )-1)) as lastname from users If it helps mark as accepted answer so other user can refer.