Split a string like 2|3 to 2 and 3 separately

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;

output: split

Now we need to split these values to two different fields and store separately like below image:

splitted

 

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 😉

Advertisements