Apply SQL Function to a Table Column Values

Question

I have a Transact SQL function SimpleSplit, which splits a string according to the delimiter. I can use it as follows:

 DECLARE @DelimitedString NVARCHAR(128)
 SET @DelimitedString = 'Processor,RAM,SSD,Ethernet'
 SELECT * FROM [dbo].[SimpleSplit](@DelimitedString, ',')

This results in:

Processor
RAM
SSD
Ethernet

As expected.

I now have a Table called PROD_TABLE with a column Descr. I would like to apply this function to each value in column Descr. I attempted the following and it does not work:

SELECT p.[ID], p.[Descr]
FROM [Amazon].[dbo].[PROD_TABLE] p
OUTER APPLY [dbo].[SimpleSplit](p.Descr, '-') d

In the output I only see the ID and the Descr Columns i.e. no results from the SimpleSplit function. However, if I attempt

SELECT *
FROM [Amazon].[dbo].[PROD_TABLE] p
OUTER APPLY [dbo].[SimpleSplit](p.Descr, '-') d

I see the results of the SimpleSplit function in the last column. Why does this query apply the function, but the previous query does not?

Answer

Thanks to mr.Rebands answer below, I realized that I needed to name the results. Hence * worked, but to explicitly name the columns I needed to do something like:

SELECT p.[ID], p.[Descr], d.[Data]
FROM [Amazon].[dbo].[PROD_TABLE] p
OUTER APPLY [dbo].[SimpleSplit](p.[Descr], '-') d

Your function returns a table - what is the column name of the SimpleSplit result table? Don't you have to include that column name in your select statement? OUTER APPLY is applied but the results not selected. — mr.Reband
– mr.Reband, Commented Jul 8, 2013 at 21:26
Wow mr.Reband. Thank you for your quick reply. I wish you had put your comment as an answer, so that I could mark it as such. — O.O.
– O.O., Commented Jul 8, 2013 at 21:32

mr.Reband · Accepted Answer · 2013-07-08 21:34:04Z

1

Your function returns a table - what is the column name of the SimpleSplit result table? You will have to include that column name in your select statement.

OUTER APPLY is applied but the results are not selected.

answered Jul 8, 2013 at 21:34

mr.Reband

2,4312 gold badges20 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Apply SQL Function to a Table Column Values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related