SQL Server 2008 T-SQL UDF 零碎 [英] SQL Server 2008 T-SQL UDF odds and ends

问题描述

我正在尝试从一列中取出一个数据字符串并将其拆分为 SQL Ser 2008 中的几个不同的列.例如:名称帐户 445566 0010020056893010445478008 AFD 369.我正在使用一个借用的空格分隔的拆分函数，它非常有效.问题是我是 T-SQL 的新手，有几个问题.

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. 如何让函数运行整个表而不仅仅是字符串?

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

这会生成一个临时表，您如何获取这些值并将它们插入到我的表中?它只是一个插入语句吗?

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

这里是脚本和用法:

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

推荐答案

要对表使用值 udf 的表，您需要 CROSS APPLY(或者可能是 OUTER APPLY 取决于您想如何处理来自 udf 的无行").这将 udf 的逐行操作应用于您的表，该表本身就是一个表

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 

插入

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

这篇关于SQL Server 2008 T-SQL UDF 零碎的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！