Lets consider a table name called sample1 in our database.
in that table we have a field called designation which stored value like 1|3 and 2|3 etc like that..here one value defines designation and another one as role..say 1 as designation and 3 as role..so next designation also belongs to same role..
now we need split these values in to two and store it in different fields of a table..
Eg: SELECT designation FROM sample1;
Now we need to split these values to two different fields and store separately like below image:
To get in above format..use this code..
SELECT SUBSTRING_INDEX(designation,’|’,1) AS degID ,SUBSTRING(designation,LOCATE(‘|’,designation)+1, LENGTH(designation) ) AS RoleID FROM sample1
Very simple..right 😉