When I join to the output of STRING_SPLIT it only joins on the first value regardless of the order of the values in the STRING_SPLIT.
select USRNAM from USERS where USRNAM in ('James.C','RON')
| USRNAM |
|---|
| JAMES.C |
| RON |
select RECIPIENTS from EMAIL_TEMPLATE where EMAIL_TEMPLATE_ID = 13
| RECIPIENTS |
|---|
| RON,JAMES.C |
select U.USRNAM, RECIPIENTS, s.VALUE from
EMAIL_TEMPLATE cross apply
string_split(RECIPIENTS,',') s
left join USERS u on s.value = u.USRNAM
where EMAIL_TEMPLATE_ID = 13
| USRNAM | RECIPIENTS | VALUE |
|---|---|---|
| RON | RON,JAMES.C | RON |
| NULL | RON,JAMES.C | JAMES.C |