The substring query works almost the same like it is used in string functions of several programming languages.
SUBSTRING ( expression ,start , length )
So in your case, if you want to get just a substring of the value in COLUMN8 (if I got you right here) than edit your query to:
select COLUMN1,COLUMN2,COLUMN3,
COLUMN4,COLUMN5,COLUMN6,(select cust_location from d_man_regn where cust_folio_no = 'VALUES' and com_ref_no = 'VALUES' AND man_regn_status in('VALUES','VALUES')),Substring(COLUMN8, start, length)
from TABLE_NAME