函数将字符串拆分为小数? [英] Function split string into decimals?

问题描述

我目前正在尝试编写一个 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

这篇关于函数将字符串拆分为小数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！