代码之家 › 专栏 › 技术社区 › Martin Moser

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

tsql sql-server-2005 sql-server sql

Martin Moser · 技术社区 · 16 年前

我目前正在尝试编写一个SQLServer2005函数,它获取一个字符串作为参数,并创建一个表,其中包含十进制值。

问题是,我必须根据参数定义十进制类型。这段不起作用的代码片段应该证明以下观点:

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

RETURNS @DecimalList TABLE (
fValue decimal(@Prec, @Scale)
)

有什么想法,怎么做?

6 回复 | 直到 16 年前

Charles Bretana 16 年前

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

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

gbn 16 年前

您不能在SQL中定义这个特殊语句。

最好的方法是使用动态SQL创建全局临时表。然后可以使用。

Paul Turner 16 年前

在T-SQL中,函数必须具有具体的返回类型。您将无法返回包含不同数据类型的表,除非您将其转换为其他进程(例如varchar)解释的基本数据类型,但这似乎会破坏函数的用途。

您可以使用动态SQL创建一个表,它允许您在表定义中指定精度和比例:

DECLARE @table NVARCHAR(MAX)
SET @table = '#DecimalTable'

DECLARE @sql NVARCHAR(MAX)
DECLARE @params NVARCHAR(MAX)

SET @sql = N'CREATE TABLE ' + @table 
        + '([fValue] DECIMAL (' + @Prec + ',' + @Scale + '))'

EXEC @sql

定义了表后,您应该能够使用CAST运算符将数据以类似的方式插入行中:

SET @sql = N'INSERT INTO ' + @table
        + 'VALUES (CAST(@Seq AS DECIMAL(' + @Prec + ',' @Scale + '))'

SET @params = N'@Seq VARCHAR(MAX)'

EXEC sp_executesql @sql, @params, @Sequence

可以说,您甚至不需要强制转换操作,因为当您插入十进制列时,SQL Server将隐式地尝试转换varchar(max)表达式。

不管是哪种方式,它都不漂亮,我建议您在使用动态SQL和它带来的所有头痛之前,先考虑一下用其他方式解决问题的可能性。

KM. 16 年前

试试这个,我只编码支持精度高达5的小数,但是如果需要,你可以增加它:

CREATE FUNCTION [dbo].[ufn_ParseDecimal]
(
    @Sequence VARCHAR(max),
    @Delim CHAR(1),
    @Prec INT,
    @Scale INT
 )
RETURNS sql_variant
AS

BEGIN

DECLARE @L  VARCHAR(max)
DECLARE @R  VARCHAR(max)

IF CHARINDEX(@Delim,@Sequence)>0
BEGIN
    SET @L=LEFT(@Sequence,CHARINDEX(@Delim,@Sequence)-1)
    SET @R=RIGHT(@Sequence,LEN(@Sequence)-CHARINDEX(@Delim,@Sequence))
END
ELSE
BEGIN
    SET @L=@Sequence
    SET @R=''
END

DECLARE @1_0 decimal(1,0)
DECLARE @1_1 decimal(1,1)

DECLARE @2_0 decimal(2,0)
DECLARE @2_1 decimal(2,1)
DECLARE @2_2 decimal(2,2)

DECLARE @3_0 decimal(3,0)
DECLARE @3_1 decimal(3,1)
DECLARE @3_2 decimal(3,2)
DECLARE @3_3 decimal(3,3)

DECLARE @4_0 decimal(4,0)
DECLARE @4_1 decimal(4,1)
DECLARE @4_2 decimal(4,2)
DECLARE @4_3 decimal(4,3)
DECLARE @4_4 decimal(4,4)

DECLARE @5_0 decimal(5,0)
DECLARE @5_1 decimal(5,1)
DECLARE @5_2 decimal(5,2)
DECLARE @5_3 decimal(5,3)
DECLARE @5_4 decimal(5,4)
DECLARE @5_5 decimal(5,5)

DECLARE @v sql_variant

IF @Prec=1 
BEGIN
    IF @Scale=0      BEGIN  SET @1_0=RIGHT(@L,1)     SET @v= @1_0 END
    ELSE IF @Scale=1 BEGIN  SET @1_1='0.'+LEFT(@R,1) SET @v= @1_1 END
END
ELSE IF @Prec=2 
BEGIN
    IF @Scale=0      BEGIN  SET @2_0=RIGHT(@L,2)                SET @v= @2_0 END
    ELSE IF @Scale=1 BEGIN  SET @2_1=RIGHT(@L,1)+'.'+LEFT(@R,1) SET @v= @2_1 END
    ELSE IF @Scale=2 BEGIN  SET @2_2=           '0.'+LEFT(@R,2) SET @v= @2_2 END
END
ELSE IF @Prec=3 
BEGIN
    IF @Scale=0      BEGIN  SET @3_0=RIGHT(@L,3)                SET @v= @3_0 END
    ELSE IF @Scale=1 BEGIN  SET @3_1=RIGHT(@L,2)+'.'+LEFT(@R,1) SET @v= @3_1 END
    ELSE IF @Scale=2 BEGIN  SET @3_2=RIGHT(@L,1)+'.'+LEFT(@R,2) SET @v= @3_2 END
    ELSE IF @Scale=3 BEGIN  SET @3_3=           '0.'+LEFT(@R,3) SET @v= @3_3 END
END
ELSE IF @Prec=4 
BEGIN
    IF @Scale=0      BEGIN  SET @4_0=RIGHT(@L,4)                SET @v= @4_0 END
    ELSE IF @Scale=1 BEGIN  SET @4_1=RIGHT(@L,3)+'.'+LEFT(@R,1) SET @v= @4_1 END
    ELSE IF @Scale=2 BEGIN  SET @4_2=RIGHT(@L,2)+'.'+LEFT(@R,2) SET @v= @4_2 END
    ELSE IF @Scale=3 BEGIN  SET @4_3=RIGHT(@L,1)+'.'+LEFT(@R,3) SET @v= @4_3 END
    ELSE IF @Scale=4 BEGIN  SET @4_4=           '0.'+LEFT(@R,4) SET @v= @4_4 END
END
ELSE IF @Prec=5 
BEGIN
    IF @Scale=0      BEGIN SET @5_0=RIGHT(@L,5)                SET @v= @5_0 END
    ELSE IF @Scale=1 BEGIN SET @5_1=RIGHT(@L,4)+'.'+LEFT(@R,1) SET @v= @5_1 END
    ELSE IF @Scale=2 BEGIN SET @5_2=RIGHT(@L,3)+'.'+LEFT(@R,2) SET @v= @5_2 END
    ELSE IF @Scale=3 BEGIN SET @5_3=RIGHT(@L,2)+'.'+LEFT(@R,3) SET @v= @5_3 END
    ELSE IF @Scale=4 BEGIN SET @5_4=RIGHT(@L,1)+'.'+LEFT(@R,4) SET @v= @5_4 END
    ELSE IF @Scale=5 BEGIN SET @5_5=           '0.'+LEFT(@R,5) SET @v= @5_5 END
END

RETURN @v

END

此示例代码使用以下函数:

      SELECT CONVERT(varchar(10),SQL_VARIANT_PROPERTY(dbo.ufn_ParseDecimal('123.4','.',4,1) , 'BaseType')),CONVERT(varchar(10),SQL_VARIANT_PROPERTY(dbo.ufn_ParseDecimal('123.4','.',4,1) , 'Precision')),CONVERT(varchar(10),SQL_VARIANT_PROPERTY(dbo.ufn_ParseDecimal('123.4','.',4,1) , 'Scale'))  ,dbo.ufn_ParseDecimal('123.4','.',4,1) 
UNION SELECT CONVERT(varchar(10),SQL_VARIANT_PROPERTY(dbo.ufn_ParseDecimal('123.45','.',5,2), 'BaseType')),CONVERT(varchar(10),SQL_VARIANT_PROPERTY(dbo.ufn_ParseDecimal('123.45','.',5,2), 'Precision')),CONVERT(varchar(10),SQL_VARIANT_PROPERTY(dbo.ufn_ParseDecimal('123.45','.',5,2), 'Scale'))  ,dbo.ufn_ParseDecimal('123.45','.',5,2)
UNION SELECT CONVERT(varchar(10),SQL_VARIANT_PROPERTY(dbo.ufn_ParseDecimal('1.234','.',5,4) , 'BaseType')),CONVERT(varchar(10),SQL_VARIANT_PROPERTY(dbo.ufn_ParseDecimal('1.234','.',5,4) , 'Precision')),CONVERT(varchar(10),SQL_VARIANT_PROPERTY(dbo.ufn_ParseDecimal('1.234','.',5,4) , 'Scale'))  ,dbo.ufn_ParseDecimal('1.234','.',5,4)

样本代码输出:

---------- ---------- ---------- ---------
decimal    4          1          123.4
decimal    5          2          123.45
decimal    5          4          1.2340

(3 row(s) affected)

David Andres 16 年前

Cast和动态SQL,尽管我不认为函数能够很好地支持后者。我一直在想:

EXEC 'SELECT 
  CAST(''' +  
         SUBSTRING(@SEQUENCE, 1, @Prec - @Scale) + 
         @Delim + 
         SUBSTRING(@SEQUENCE, @Prec - @Scale + 1) + 
       "'' 
       AS DECIMAL(' + @Prec + ', ' + @Scale + ')'

MatBailie 16 年前

正如其他人提到的,表值用户定义函数必须为每个字段具有特定的返回类型。

我能绕过的方法就是稍微改变一下设计。让函数将[sequence]分解为字符串表。还不进行转换…

CREATE FUNCTION [dbo].[ufn_ParseList] (
    @Sequence VARCHAR(MAX),
    @Delim CHAR(1)
)

RETURNS @List TABLE (
    id INT IDENTITY(1,1),
    item VARCHAR(MAX)   -- You may want to use something smaller than (MAX)
)

然后,一旦您有了一个字符串表,就应用您需要的转换。正如其他人所提到的,这很可能是由动态SQL实现的。

然而,动态SQL在代码主体中的存在可能是一个真正的痛苦…