To return text value between the first and second semicolons counting from the right of a string

I have a field in a table which consist of a string of values separated by semi-colons, e.g. apple; banana; orange; pear

I have been trying to build a SELECT statement to return the second group in between first and second semi-colons counting from the right (and if there is only 1 semi-colon will return null for now).

For example, orange.

I have been testing and trying ChatGPT but the closest I have gotten is of below, which returns everything right of the second semi-colon counting from the right.

As example: orange; pear

I just cannot figure out what I should do to not show anything value right of the first semi-colon on the right. Or could it be my dataset issue, as I do see sometimes commas were used instead of semi-colons but that could be another story too.

LTRIM(RTRIM(
           CASE
               -- Ensure there are more than two semicolons
               WHEN LEN(@string) - LEN(REPLACE(@string, ';', '')) >= 2
               THEN SUBSTRING(
                   @string,
                   LEN(@string) - CHARINDEX(';', REVERSE(@string), CHARINDEX(';', REVERSE(@string)) + 1) + 2,
                   CHARINDEX(';', REVERSE(@string), CHARINDEX(';', REVERSE(@string)) + 1) - 1
               )
               ELSE ''
           END
       )) AS SecondGroupFromRight

Would really appreciate some help. Many thanks in advance

This answer is for sql server
Another way is to split them into seperate rows, and then retrieve the 2nd last row
We can use the row_number() to give each seperate row a sequential number

declare @split nchar(1) = ';'
declare @val nvarchar(2000) = 'apple; banana; orange; pear'

select t.value
from   ( select trim(value) as value,
                ROW_NUMBER() over(order by (select 1)) as rn,
                (select count(value) from string_split(@val, @split)) as total
                from   string_split(@val, @split)
       ) t
where t.total > 2
and   t.rn = t.total - 1

See this dbFiddle

• trim(value) to get rid of the space in front of the values
• order by (select 1) in the row_number to avoid sorting in row_number()
• select count(value)... to retrieve the total numbers of values
• now you can simply say t.rn = t.total - 1 to retrieve the 2nd last row
• where t.total > 2 so you have no result when less then 3 values

And while this is all nice, but how to use this when @val would be a column in a table ?

well, like this

declare @split nchar(1) = ';'
  
select test.id,
       ( select t.value
         from   ( select trim(value) as value,
                         ROW_NUMBER() over(order by (select 1)) as rn,
                         (select count(value) from string_split(test.fruit, @split)) as total
                         from   string_split(test.fruit, @split)
                ) t
          where t.total > 2
          and   t.rn = t.total - 1
       ) value,
       test.othercolumn
from   test

See this dbFiddle for a working example

result could be