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 |
create table test (id int, fruit nvarchar(2000), othercolumn varchar(20));
insert into test values
(1, 'apple; banana; orange; pear', 'hello world'),
(2, 'mango; grapes; melon', 'how are you');
--select * from test;
2 rows affected
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
| id | value | othercolumn |
|---|---|---|
| 1 | orange | hello world |
| 2 | grapes | how are you |
drop table test;