Remove End Numeric from String Column in SQL Server

select SUBSTRING('VOP1', 0, PATINDEX('%[0-9]%', 'VOP1'));
output: VOP


For HiveQL,
split(col_name,'_')[1]