Issue in data in 3rd column

Solution 1:

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,';');