By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
declare @split nchar(1) = ';'
declare @val nvarchar(2000) = 'apple; banana; orange; pear'
--declare @val nvarchar(2000) = 'apple; banana; orange'
--declare @val nvarchar(2000) = 'apple; banana'
--declare @val nvarchar(2000) = 'apple'
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
| value |
|---|
| orange |