Get table from split function

[tshad]

I have a function to split comma delimited strings. It returns a table.

CREATE FUNCTION dbo.SplitBigInt ( @strString varchar(4000)) 
RETURNS  @Result TABLE(Value bigint) 
AS 
begin 
    WITH StrCTE(start, stop) AS 
    ( 
      SELECT  1, CHARINDEX(',' , @strString ) 
      UNION ALL 
      SELECT  stop + 1, CHARINDEX(',' ,@strString  , stop + 1) 
      FROM StrCTE 
      WHERE stop > 0 
    ) 
    insert into @Result 
    SELECT   SUBSTRING(@strString , start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS stringValue 
    FROM StrCTE 
	
	return
end

I can use the code fine doing the following:

SELECT * FROM SplitBigInt('100,200,300,400')

This will give me a table with 4 rows.

But I can't figure out how to use the function with a table that has a column that is a comma delimited string.

It would return n-tables. I want one table with all the values in one column in one table. Just like the above SELECT does.

If OBJECT_ID('tempdb..#SomeLists') IS NOT NULL
	DROP TABLE #SomeLists

Create Table #SomeLists
(
	ID int,
	AList varchar(100)
)

INSERT #SomeLists (ID, AList) VALUES(1, '80,15,200,80')	
INSERT #SomeLists (ID, AList) VALUES(2, '200,250,80,920')	
INSERT #SomeLists (ID, AList) VALUES(3, '800,850,22,100')	
INSERT #SomeLists (ID, AList) VALUES(4, '900,200,150')	

SELECT * from #SomeLists

How would I do my select to select all these rows and end up with a one column table with one of these values in each column. For example:

AList
80
15
200
80
200
250
80
etc.

Thanks,

Tom

 

[gmmastros]

You can use Cross Apply to accommodate this.

SELECT	#SomeLists.Id,
        CommaList.Value
from	#SomeLists 
	Cross Apply dbo.SplitBigInt(#SomeLists.AList) As CommaList

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tshad]

That works but I'm not sure why.

Doesn't the CrossApply have to be a query?

Thanks,

Tom

 

[gmmastros]

What's a query??? Maybe the cross apply functionality only requires a result set (one or more columns and one or more rows). Isn't that what your function returns?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tshad]

Yes.

But I normally see a cross apply with a Select in the parens.

In this case, the function is returning a table (result set). What I was surprised at was that each row of the SELECT would return a different table but they are all combined into one table for the final result.

 

[Olaf Doschke]

>What I was surprised at was that each row of the SELECT would return a different table but they are all combined into one table for the final result.

Again, what George already said: What is a query? think.

If you have a sub query in place of the function, this sub query will also be a table per record of the main query. Big surprise?

Bye, Olaf.

 

[tshad]

Why does Split funtion return a column with a name of "Value"?

You can actually change this to:

SELECT	#SomeLists.Id
from	#SomeLists 
	Cross Apply dbo.SplitBigInt(#SomeLists.AList) As CommaList

And you would have a column with the name "Value" here.

Thanks,

Tom

 

[fredericofonseca]

Why does Split funtion return a column with a name of "Value"?

because your function

CREATE FUNCTION dbo.SplitBigInt ( @strString varchar(4000))
RETURNS @Result TABLE(Value bigint)
AS

declares a table with a single field called "Value"

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[tshad]

Of course it does.

Little brain fade there.

Thanks