MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table test(id int,name varchar(1000));
insert into test(id,name)
select 1,'200 DAVID' name
union all
select 2,'100 JOHN'
union all
select 3,'100-1 SHAWN'
union all
select 4,'100-1/1 PETER'
union all
select 5,'100-1/1/1 ALVIS'
union all
select 6,'100-1/1/3 PIRES'
union all
select 7,'100-1/1/10 ROBERT'
union all
select 8,'100-1/1/11 HENRY'
union all
select 9,'100-1/2 BILLY'
union all
select 10,'100-1/2/1 JIOUS'
union all 
select 11,'700 PERKHIDMATAN PERBANDARAN'
-- g
CREATE FUNCTION locate_offset(str text,substr text, offset int)
RETURNS int
DETERMINISTIC
BEGIN
 DECLARE loc INT DEFAULT 1;
 DECLARE i INT DEFAULT 0;
 WHILE(i<offset) DO
 SET loc=LOCATE(substr, str, loc+1);
 SET i=i+1;
 END WHILE;
 RETURN(loc);
END
-- step 1 removing the name part e.g ' ALVIS' from '100-1/1/1 ALVIS'
select t.*,substring_index(name,' ',1) modified_name
          ,locate_offset(name,'-',1) col1_col2
          ,locate_offset(name,'/',1) col3
          ,locate_offset(name,'/',2) col4
  from test t


id
name
modified_name
col1_col2
col3
col4


1
200 DAVID
200
0
0
0

2
100 JOHN
100
0
0
0

3
100-1 SHAWN
100-1
4
0
0

4
100-1/1 PETER
100-1/1
4
6
0

5
100-1/1/1 ALVIS
100-1/1/1
4
6
8

6
100-1/1/3 PIRES
100-1/1/3
4
6
8

7
100-1/1/10 ROBERT
100-1/1/10
4
6
8

8
100-1/1/11 HENRY
100-1/1/11
4
6
8

9
100-1/2 BILLY
100-1/2
4
6
0

10
100-1/2/1 JIOUS
100-1/2/1
4
6
8

11
700 PERKHIDMATAN PERBANDARAN
700
0
0
0


-- step 2 derive col1 and col2
select t.*,substring_index(modified_name,'-',1) col1
          ,case when    locate('-', modified_name) > 0 
                    and locate('/', modified_name) > 0
                then substr( modified_name
                           , locate('-', modified_name) + 1
                           ,(locate('/', modified_name) - (locate('-', modified_name) + 1)))
                when locate('-', modified_name) > 0
                then substr(modified_name, locate('-', modified_name) + 1)
           end as col2
from (select t.*,substring_index(name,' ',1) modified_name
        from test t
     ) t
     


id
name
modified_name
col1
col2


1
200 DAVID
200
200
null

2
100 JOHN
100
100
null

3
100-1 SHAWN
100-1
100
1

4
100-1/1 PETER
100-1/1
100
1

5
100-1/1/1 ALVIS
100-1/1/1
100
1

6
100-1/1/3 PIRES
100-1/1/3
100
1

7
100-1/1/10 ROBERT
100-1/1/10
100
1

8
100-1/1/11 HENRY
100-1/1/11
100
1

9
100-1/2 BILLY
100-1/2
100
1

10
100-1/2/1 JIOUS
100-1/2/1
100
1

11
700 PERKHIDMATAN PERBANDARAN
700
700
null


select t.id,t.name
from
(
select t.*, cast((case when col1_col2_ref > 0 
                      then 
                        substring_index(modified_name,'-',1)
                  else 
                     modified_name
                  end
                  ) as unsigned) col1
          , cast((case when    col1_col2_ref > 0
                           and col3_ref > 0
                       then 
                          substr(modified_name,(col1_col2_ref + 1),(col3_ref - (col1_col2_ref + 1)))
                       when col1_col2_ref > 0
                       then 
                         substr(modified_name,(col1_col2_ref + 1))
                  end) as unsigned) col2
          , cast((case when    col3_ref > 0
                           and col4_ref > 0
                       then 
                         substr(modified_name,(col3_ref + 1),(col4_ref - (col3_ref + 1)))
                       when col3_ref > 0
                       then 
                         substr(modified_name,(col3_ref + 1))
                  end) as unsigned) col3
          , cast((case when col4_ref > 0
                        then 
                         substr(modified_name,(col4_ref + 1))
                   end) as unsigned) col4
  from
(
select t.*,substring_index(name,' ',1) modified_name
          ,locate('-',name,1) col1_col2_ref
          ,locate('/',name,1) col3_ref
          ,locate('/',name,locate('/',name,1)+1) col4_ref
  from test t
) t
) t
order by col1,col2,col3,col4


id
name


2
100 JOHN

3
100-1 SHAWN

4
100-1/1 PETER

5
100-1/1/1 ALVIS

6
100-1/1/3 PIRES

7
100-1/1/10 ROBERT

8
100-1/1/11 HENRY

9
100-1/2 BILLY

10
100-1/2/1 JIOUS

1
200 DAVID

11
700 PERKHIDMATAN PERBANDARAN


select t.*,substring_index(name,' ',1) modified_name
          ,locate('-',name,1) col1_col2_ref
          ,locate('/',name,1) col3_ref
          ,locate('/',name,locate('/',name,1)+1) col4_ref
  from test t


id
name
modified_name
col1_col2_ref
col3_ref
col4_ref


1
200 DAVID
200
0
0
0

2
100 JOHN
100
0
0
0

3
100-1 SHAWN
100-1
4
0
0

4
100-1/1 PETER
100-1/1
4
6
0

5
100-1/1/1 ALVIS
100-1/1/1
4
6
8

6
100-1/1/3 PIRES
100-1/1/3
4
6
8

7
100-1/1/10 ROBERT
100-1/1/10
4
6
8

8
100-1/1/11 HENRY
100-1/1/11
4
6
8

9
100-1/2 BILLY
100-1/2
4
6
0

10
100-1/2/1 JIOUS
100-1/2/1
4
6
8

11
700 PERKHIDMATAN PERBANDARAN
700
0
0
0

id	name	modified_name	col1_col2	col3	col4
1	200 DAVID	200	0	0	0
2	100 JOHN	100	0	0	0
3	100-1 SHAWN	100-1	4	0	0
4	100-1/1 PETER	100-1/1	4	6	0
5	100-1/1/1 ALVIS	100-1/1/1	4	6	8
6	100-1/1/3 PIRES	100-1/1/3	4	6	8
7	100-1/1/10 ROBERT	100-1/1/10	4	6	8
8	100-1/1/11 HENRY	100-1/1/11	4	6	8
9	100-1/2 BILLY	100-1/2	4	6	0
10	100-1/2/1 JIOUS	100-1/2/1	4	6	8
11	700 PERKHIDMATAN PERBANDARAN	700	0	0	0

id	name	modified_name	col1	col2
1	200 DAVID	200	200	null
2	100 JOHN	100	100	null
3	100-1 SHAWN	100-1	100	1
4	100-1/1 PETER	100-1/1	100	1
5	100-1/1/1 ALVIS	100-1/1/1	100	1
6	100-1/1/3 PIRES	100-1/1/3	100	1
7	100-1/1/10 ROBERT	100-1/1/10	100	1
8	100-1/1/11 HENRY	100-1/1/11	100	1
9	100-1/2 BILLY	100-1/2	100	1
10	100-1/2/1 JIOUS	100-1/2/1	100	1
11	700 PERKHIDMATAN PERBANDARAN	700	700	null