函数将字符串拆分为小数?

本文介绍了函数将字符串拆分为小数?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我目前正在尝试编写一个 SQL Server 2005 函数，该函数获取一个字符串作为参数并从中创建一个包含十进制值的表.

I'm currently trying to write a SQL Server 2005 function, which gets a string as parameter and creates a table with decimal values out of it.

问题是，我必须根据参数定义十进制类型.这个不工作的片段应该证明这个想法:

The problem is, that I have to define the decimal type based on parameters. This not working snippet should demonstrate the idea:

CREATE FUNCTION [dbo].[ufn_ParseDecimal] ( @Sequence VARCHAR(max), @Delim CHAR(1), @Prec INT, @Scale INT ) RETURNS @DecimalList TABLE ( fValue decimal(@Prec, @Scale) )

任何想法，如何做到这一点?

Any ideas, how this could be done?

推荐答案

这是一个将任何文本字符串解析为值表的通用函数...您可以轻松地使用它来完成您想要完成的工作:

This is a generic function to parse any text string into a table of values... You can easily use it to do what you are trying to accomplish:

ALTER FUNCTION [dbo].[ParseTextString] (@S Text, @delim VarChar(5)) Returns @tOut Table (ValNum Integer Identity Primary Key, sVal VarChar(8000)) As Begin Declare @dLLen TinyInt -- Length of delimiter Declare @sWin VarChar(8000) -- Will Contain Window into text string Declare @wLen Integer -- Length of Window Declare @wLast TinyInt -- Boolean to indicate processing Last Window Declare @wPos Integer -- Start Position of Window within Text String Declare @sVal VarChar(8000) -- String Data to insert into output Table Declare @BtchSiz Integer -- Maximum Size of Window Set @BtchSiz = 7900 -- (Reset to smaller values to test routine) Declare @dPos Integer -- Position within Window of next Delimiter Declare @Strt Integer -- Start Position of each data value within Window -- ------------------------------------------------------------------------- If @delim is Null Set @delim = '|' If DataLength(@S) = 0 Or Substring(@S, 1, @BtchSiz) = @delim Return -- --------------------------- Select @dLLen = Len(@delim), @Strt = 1, @wPos = 1, @sWin = Substring(@S, 1, @BtchSiz) Select @wLen = Len(@sWin), @wLast = Case When Len(@sWin) = @BtchSiz Then 0 Else 1 End, @dPos = CharIndex(@delim, @sWin, @Strt) -- ------------------------------------ While @Strt <= @wLen Begin If @dPos = 0 -- No More delimiters in window Begin If @wLast = 1 Set @dPos = @wLen + 1 Else Begin Set @wPos = @wPos + @Strt - 1 Set @sWin = Substring(@S, @wPos, @BtchSiz) -- ---------------------------------------- Select @wLen = Len(@sWin), @Strt = 1, @wLast = Case When Len(@sWin) = @BtchSiz Then 0 Else 1 End, @dPos = CharIndex(@delim, @sWin, 1) If @dPos = 0 Set @dPos = @wLen + 1 End End -- ------------------------------- Set @sVal = LTrim(Substring(@sWin, @Strt, @dPos - @Strt)) Insert @tOut (sVal) Values (@sVal) -- ------------------------------- -- Move @Strt to char after last delimiter Set @Strt = @dPos + @dLLen Set @dPos = CharIndex(@delim, @sWin, @Strt) End Return End