SQL Server 2008 T-SQL UDF odds and ends

问题:

I am trying to take a data string from one column and split it into several different columns in SQL Ser 2008. Eample: Name Account 445566 0010020056893010445478008 AFD 369. I'm useing a borrowed space delimited split function which workS great. The problem is I'm new to T-SQL and have a few questions.

1. How do I get the function to run through a full table not just a string literal?

2. This generates a temparary table how do you take these values and insert them into my table? Is it just an insert statement?

Here is the script and usage:

CREATE FUNCTION [dbo].[Split] 
(    
 @String varchar(max) 
,@Delimiter char 
) 
RETURNS @Results table 
( 
 Ordinal int 
,StringValue varchar(max) 
) 
as 
begin 

set @String = isnull(@String,'') 
set @Delimiter = isnull(@Delimiter,'') 

declare 
 @TempString varchar(max) = @String 
,@Ordinal int = 0 
,@CharIndex int = 0 

set @CharIndex = charindex(@Delimiter, @TempString) 
while @CharIndex != 0 begin      
    set @Ordinal += 1        
    insert @Results values 
    ( 
     @Ordinal 
    ,substring(@TempString, 0, @CharIndex) 
    )        
    set @TempString = substring(@TempString, @CharIndex + 1, len(@TempString) - @CharIndex)      
    set @CharIndex = charindex(@Delimiter, @TempString) 
end 

if @TempString != '' begin 
    set @Ordinal += 1  
    insert @Results values 
    ( 
     @Ordinal 
    ,@TempString 
    ) 
end 

return 
end 

--USAGE
select 
s.* 
from dbo.Split('Name Account 445566 0010020056893010445478008 AFD 369', ' ') as s 
where rtrim(s.StringValue) != '' 
GO

回答1:

To use a table valued udf against a table, you need CROSS APPLY (or maybe OUTER APPLY depending on how you want to deal with "no rows" from the udf). This applies the row-by-row operation of the udf against your table which itself is a table

SELECT
   *
FROM
   mytable M
   CROSS APPLY
   [dbo].[Split] (M.TheColumn) S 

To INSERT

INSERT AnotherTable (col1, col2, ...)
SELECT
   col1, col2, ...
FROM
   mytable M
   CROSS APPLY
   [dbo].[Split] (M.TheColumn) S