从 T-SQL 分隔字符串解析整数值的更好方法 [英] A better way to parse integer values from a T-SQL delimited string

问题描述

我有一个 SQLServer2008 R2 存储过程，其中包含一个用于从分隔字符串中解析出整数的算法.

I have a SQLServer2008 R2 Stored Procedure that contains an algorithm for parsing out integers from a delimited string.

这是我为循环遍历分隔字符串并提取分隔字符串中可能存在的任何数字而编写的 SQL 代码示例:

Here's an example of the SQL code that I made for looping through the delimited string and extracting any numbers that may exist in the delimited string:

-- Create a delimited list for testing
DECLARE @NumericList nvarchar(MAX) = N'1, 33,44 ,55, foo ,666,77 77,8,bar,9,10'

-- Declare the delimiter
DECLARE @ListDelimiter VARCHAR(1) = ','

-- Remove white space from the list
SET @NumericList = REPLACE(@NumericList, ' ','');

-- Var that will hold the value of the delimited item during the while-loop
DECLARE @NumberInScope VARCHAR(MAX)

WHILE(LEN(@NumericList) > 0)
BEGIN
    -- Get the value to the left of the first delimiter.
    IF(CHARINDEX(@ListDelimiter, @NumericList) > 0)
        SET @NumberInScope = LEFT(@NumericList, CHARINDEX(@ListDelimiter, @NumericList))
    ELSE
        SET @NumberInScope = @NumericList   

    -- Remove the @NumberInScope value from the @NumericList
    SET @NumericList = RIGHT(@NumericList, LEN(@NumericList) - LEN(@NumberInScope))

    -- Remove the delimiter from the @NumberInScope
    SET @NumberInScope = REPLACE(@NumberInScope,@ListDelimiter,'')

    -- Print only the integer values
    IF(ISNUMERIC(@NumberInScope) = 1)
    BEGIN
        PRINT @NumberInScope
    END 
END

上面的代码运行良好，但在查看代码后，我觉得必须有一种更简洁的方式来做同样的事情.换句话说，是否有任何我可以忽略的字符串函数(或任何新的 R2 函数，也许)我可以实现它会缩小代码并希望更易于阅读?

The code above works fine, but after reviewing the code it seems to me that there's got to be a more concise way of doing the same thing. In other words, is there any string functions (or any new R2 function, maybe) that I'm overlooking that I can implement that would shrink the code and, hopefully, be easier to read?

推荐答案

这是代码，您也可以创建一个拆分"函数并使用它

Here is the code, you can also create a 'split' function and use that

DECLARE @NumericList nvarchar(max) = N'1, 33,44 ,55, foo ,666,77 77,8,bar,9,10'

;WITH cte as (
SELECT CAST(1 as bigint) p1,  CHARINDEX(',', @NumericList+',') p2, 
CAST(null as Nvarchar(max)) NumberInScope 
UNION ALL
SELECT p2 + 1, CHARINDEX(',',@NumericList+',', p2 + 1), 
SUBSTRING(@NumericList, p1, p2-p1) 
FROM cte WHERE p2>0
)
SELECT NumberInScope from cte WHERE isnumeric(NumberInScope) > 0
OPTION (MAXRECURSION 0)

这篇关于从 T-SQL 分隔字符串解析整数值的更好方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！