SQL join on string_split values

Question

So, I have a column value something like this:

1,15,32,64

And another table (called infoTable) where the identifier and text is in seperate columns. What would be the best way to get the Data of the infoTable into an select from the mainTable ?

I've thought about string_split but cant find anything on how i should start with that.

Im currently using MS SQL Server Manager Studio.

Microsoft SQL Server 2017 (RTM-GDR) (KB4583456) - 14.0.2037.2 (X64) Nov 2 2020 19:19:59 Copyright (C) 2017 Microsoft Corporation Express Edition (64-bit) on Windows 10 Pro 10.0 (Build 18363: ) — Marcel H., Jan 19 '21 at 08:56

score 2 · Answer 1 · answered Jan 19 '21 at 12:42

2

I think you are looking for cross apply:

select t.*, i.text
from maintable t cross apply
     string_split(t.ids, ',') s join
     infotable i 
     on i.id = s.id;

answered Jan 19 '21 at 12:42

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · answered Jan 19 '21 at 08:58

0

below is the join

select i.* FROM infoTable i
INNER JOIN string_split('1,15,32,64',',') sp
on i.columname=sp.value

answered Jan 19 '21 at 08:58

Zaynul Abadin Tuhin

31,407
5
33
63

I dont quite get it. Take a look at the code in the pastebin: https://pastebin.pl/view/8b9105ef How should i implement it into that? – Marcel H. Jan 19 '21 at 09:11
1

@MarcelH. create another question with your orginal problem – Zaynul Abadin Tuhin Jan 19 '21 at 09:20

SQL join on string_split values

2 Answers2

Linked