How to Extract Data Between Delimiters in NVarchar Field in SQL Server

sql serversql-server-2008-r2t-sql

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

Best Answer

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)
Related Question