Split string to array using delimiter, getting second to last element in SELECT Statement

As an alternative you can try this:.

--A mockup table with some test data to simulate your issue

DECLARE @mockupTable TABLE (ID INT IDENTITY, YourColumn VARCHAR(50));
INSERT INTO @mockupTable VALUES 
 ('H-01-01-02-01') 
,('BLE-01-03-01')
,('H-02-05-1.1-03');

--The query

SELECT CastedToXml.value('/x[sql:column("CountOfFragments")-1][1]','nvarchar(10)') AS TheWantedFragment
FROM @mockupTable t
CROSS APPLY(SELECT CAST('<x>' + REPLACE(t.YourColumn,'-','</x><x>') + '</x>' AS XML))A(CastedToXml)
CROSS APPLY(SELECT CastedToXml.value('count(/x)','int')) B(CountOfFragments);

The idea in short:

The first APPLY will transform the string to a XML like this

<x>H</x>
<x>01</x>
<x>01</x>
<x>02</x>
<x>01</x>

The second APPLY will xquery into this XML to get the count of fragments. As APPLY will add this as a column to the result set, we can use the value using sql:column() to get the wanted fragment by its position.

As I wrote in my comment - using charindex with reverse.

First, create and populate sample table (Please save us this step in your future questions):

DECLARE @T AS TABLE
(
    Col Varchar(100)
);

INSERT INTO @T (Col) VALUES
('H-01-01-02-01'),
('BLE-01-03-01'),
('H-02-05-1.1-03');

The query:

SELECT  Col, 
        LEFT(RIGHT(Col, AlmostLastDelimiter-1), AlmostLastDelimiter - LastDelimiter - 1) As SecondToLast
FROM @T 
CROSS APPLY (SELECT CharIndex('-', Reverse(Col)) As LastDelimiter) As A
CROSS APPLY (SELECT CharIndex('-', Reverse(Col), LastDelimiter+1) As AlmostLastDelimiter) As B

Results:

Col             SecondToLast
H-01-01-02-01   02
BLE-01-03-01    03
H-02-05-1.1-03  1.1

Similar to Zohar's solution, but using CTEs instead of CROSS APPLY to prevent redundancy. I personally find this easier to follow, as you can see what happens in each step. Doesn't make it a better solution though ;)

DECLARE @strings TABLE (data VARCHAR(50));
INSERT INTO @strings VALUES ('H-01-01-02-01') , ('BLE-01-03-01'), ('H-02-05-1.1-03');
WITH rev AS (
    SELECT
        data,
        REVERSE(data) AS reversed
    FROM
        @strings),
first_hyphen AS (
    SELECT
        data,
        reversed,
        CHARINDEX('-', reversed) + 1 AS first_pos
    FROM
        rev),
second_hyphen AS (
    SELECT
        data,
        reversed,
        first_pos,
        CHARINDEX('-', reversed, first_pos) AS second_pos
    FROM
        first_hyphen)
SELECT
    data,
    REVERSE(SUBSTRING(reversed, first_pos, second_pos - first_pos)) AS result
FROM
    second_hyphen;

Results:

data            result
H-01-01-02-01   02
BLE-01-03-01    03
H-02-05-1.1-03  1.1

Try this

declare @input NVARCHAR(100)
declare @dlmt NVARCHAR(3);
declare @pos INT = 2
SET @input=REVERSE(N'H-02-05-1.1-03');
SET @dlmt=N'-';
SELECT 
    CAST(N'<x>' 
    + REPLACE(
                (SELECT REPLACE(@input,@dlmt,'#DLMT#') AS [*] FOR XML PATH(''))
                    ,N'#DLMT#',N'</x><x>'
              ) + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)');