I can't understand this error. Can anyone explain? Its giving error when trying to insert data into col3 The string part "Passport 1084010(Egypt)" should enter easily.

DECLARE @text2 varchar(max) = 'DOB 19 Jun 1951; POB Giza, Egypt; Passport 1084010(Egypt); alt. Passport 19820215; Operational and Military Leader of JIHAD GROUP.';
DECLARE @table2001 table(
id int identity(1,1) primary key,
col1 varchar(max),
col2 varchar(max),
col3 varchar(max),
col4 varchar(max),
col5 varchar(max)
);

select value from string_split(@text2,';')

insert into @table2001 (col1,col2,col3,col4,col5)
values
(
(select value from string_split(@text2,';') where value like '%DOB%'),
(select value from string_split(@text2,';') where value like '%POB%'),
(

select value from string_split(@text2,';') where value like ' %Passport%'

),
(select value from string_split(@text2,';') where value like '%alt.%'),
(select value from string_split(@text2,';') where value like '%ope%')
)

select * from @table2001;

RESULT:

(5 rows affected)
Msg 512, Level 16, State 1, Line 13
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

(0 rows affected)

Completion time: 2022-01-20T13:11:51.3132613 05:00

CodePudding user response：

It's because the selects for the values can have more than 1 row

For example

select value from string_split(@text2,';')
where value like ' %Passport%'

Returns

You could TOP 'em

select TOP 1 value from string_split(@text2,';')
where value like ' %Passport%'

Or use conditional aggregation.

insert into @table2001 (col1,col2,col3,col4,col5)
select
max(case when value like '%DOB%' then value end), 
max(case when value like '%POB%' then value end), 
max(case when value like '%Passport%' then value end), 
max(case when value like '%alt.%' then value end), 
max(case when value like '%ope%' then value end)
from string_split(@text2,';');