Extract Data Between Delimeter In NVarchar Field
This seems to work:

declare @name varchar(20) = 'name_Test_1'

select SUBSTRING(@name,CHARINDEX('_',@name,0)+1, (CHARINDEX('_',@name,CHARINDEX('_',@name,0)+1)) - CHARINDEX('_',@name,0)-1)