join via a string of integers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

join via a string of integers

Author

Topic

ultradiv
Starting Member

41 Posts

Posted - 2010-04-26 : 10:32:45

Hi,
there are two tables but they are related by a list of INT's in a comma seperated varchar column. I need to join these tables for a result set.

To show you what is required this will create and populate three tables
--###########################################
#Table_S contains the column with the string of INTs
#Table_P contains the data (column IDp relates to the integers in the string of INTs
and #Table_R is table displaying the results I need.

declare @c int

CREATE TABLE #Table_S(
IDs int IDENTITY(1,1) NOT NULL,
strInts varchar(50) NULL)

Insert #Table_S(strInts)
values('1,3,6,11')

Insert #Table_S(strInts)
values('2,4,7,8,9,10')

CREATE TABLE #Table_P(
IDp int IDENTITY(1,1) NOT NULL,
ProName varchar(10) NULL)

set @c=1
while @c<21
begin
Insert #Table_P(ProName)
values('line'+cast(@c as varchar(10)))
set @c=@c+1
end

CREATE TABLE #Table_R(
IDp int,
ProName varchar(50)
)

Insert #Table_R(IDp,ProName)
select IDp, 'line'+cast(IDp as varchar(10)) from #Table_P where IDp in (1,3,6,11)
Insert #Table_R(IDp,ProName)
select IDp, 'line'+cast(IDp as varchar(10)) from #Table_P where IDp in (2,4,7,8,9,10)

select * from #Table_S

drop table #Table_S

select * from #Table_P

drop table #Table_P

select * from #Table_R

drop table #Table_R

--##############################################

Somehow I need a query that will perform this operation quickly and regularly.

I have a function that accepts a string of INTs and outputs a table of the integers:
CREATE FUNCTION [dbo].[iter_intlist_to_table] (@list varchar(4000))
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
number int NOT NULL) AS
BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@str nvarchar(4000),
@tmpstr nvarchar(4000),
@leftover nvarchar(4000)

SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))
SET @textpos = @textpos + @chunklen

SET @pos = charindex(',', @tmpstr)
WHILE @pos > 0
BEGIN
SET @str = substring(@tmpstr, 1, @pos - 1)
INSERT @tbl (number) VALUES(convert(int, @str))
SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
SET @pos = charindex(',', @tmpstr)
END

SET @leftover = @tmpstr
END

IF ltrim(rtrim(@leftover)) <> ''
INSERT @tbl (number) VALUES(convert(int, @leftover))

RETURN
END

--use: iter_intlist_to_table(@ids) @ids as in the form of '1,2,3,4,5,6'

I'm sure I could use this function but how?

Many thanks
Andy

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-26 : 11:54:46

[code]
select number, c.proname from #table_s a
cross apply
iter_intlist_to_table(a.strInts) as b
inner join #table_p c
on b.number = c.idp
order by a.ids
[/code]

ultradiv
Starting Member

41 Posts

Posted - 2010-04-26 : 12:13:31

hanbingl

Great stuff!!
It seems that you get the crown, thank you.

Andy

ultradiv
Starting Member

41 Posts

Posted - 2010-04-27 : 07:23:43

a slightly faster method of comma seperated string to table conversion using xml:

create function SplitStringX(@f varchar(max)) returns table
as
return
select v.value('.','int') as value
from (select convert(xml, '<v>'+replace(@f,',','</v><v>')+'</v>') as f ) t1
cross apply t1.f.nodes('v') r(v)
go

courtesy of Stefan_G over on sqlservercentral.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources