substring function to create new column

[Chandan]

Hi There,

I am expecting the final table should be like below:

Output:
id	     direction	           position
aa11bc  	north, south, east, west	north
bb22cd  	north, south, east, west	north
cc33ef  	north, south, east, west	south
dd44gh	    north, south, east, west	south
ee55ij	    north, south, east, west	east
ff66kl	    north, south, east, west	east
gg77mn	    north, south, east, west	west
hh88op	    north, south, east, west	west

Please advise.