Extract Data Between Delimeter In NVarchar Field I have a nvarchar(1000) data type field that holds data like this
name_Test_1
 name_Test1_2
 name_Test2_3

Now the data I want to extract is what is between the _ so the data I would want extracted would be

Test
Test1
Test2


I tried the below syntax, but since the string has two _ in it then the data I have returned is 1, 2, 3 - how should this query be changed so that I get the middle string?

Declare @Red Table (fname nvarchar(1000))

Insert Into @Red (fname) Values
('name_Test_1'), ('name_Test1_2'), ('name_Test2_3') 

Select
RIGHT(fname,CHARINDEX('_',REVERSE(fname))-1) as RightHalf
FROM @Red
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)