使用表值函数实现的 Transact Sql 版本的 C strtok()
5.00/5 (2投票s)
发布一个 Transact-SQL 函数，用于解析包含分隔列表的字符串，类似于 ANSI C 语言的 strtok() 函数。
引言
我应该承认：我是一个 C/C++ 面向对象的人，在我的职业生涯后期才接触 SQL Server。 那些经历过类似道路的人知道这意味着什么。 遇到了标准模板库后，编程存储过程就像回到了 Visual Basic 之前的 Basic 时代，或者 Turbo Pascal 之前的 Pascal 时代... 但你知道，改变一个组织的文化不是你能决定的。 只需要适应并贡献你从经验中获得的最佳能力。
Sql Server 存储过程让我恼火的第一件事是完全不支持使用集合或列表作为参数。 我只能想起 Perl... 原则上，SELECT 语句返回一个列表，那么为什么我不能也输入一个列表呢？ 更不用说为什么存储过程不能捕获被调用的存储过程生成的列表...？ 我知道在最新版本的 Sql Server 存储过程中支持表类型作为参数。 我还没有测试过这个功能。 但是对于像我这样仍在使用 Sql Serveer 2005 的人来说，本文可能很有用。 即使您正在使用 2005 年后的 sql server 版本，您也可能会遇到处理此类列表的代码，或者您可能喜欢逗号分隔列表的松散类型性的快速而肮脏的方法。
所以我采用了常见的解决方法，即使用 Varchar 参数来传递字符串化项的逗号分隔字符串。 问题是解析列表。 第一次处理解析时，我很快想到了来自美好 C 时代的有用 strtok() 函数。 在第二次遇到问题后，我决定编写自己的 sql 版本 strtok()
一些 ANSI C 背景知识
strtok() 是核心字符串库中一个非常有用的 C 函数。 您将目标字符串和预期的分隔符作为参数传递，您将获得直到分隔符第一次出现之前的子字符串。 这就是所谓的“token”。
s = strtok("one,two,three", ',')
将返回“one”。 最好的部分是，随后对该函数的调用返回以下 token“two”、“three”，最后返回 null 以表示不再有 token。 这使得 strtok() 适合在循环中使用
s = strtok("one,two,three", ',');
while (s != null)
{
s = strtok(null, ',');
// do something with the token
}
请注意，只有在第一次调用时才指定输入字符串。 在随后的调用中，您传递 null 以指示使用相同的字符串并查找下一个 token... 这听起来有点奇怪。 实际上，strtok() 修改了输入字符串并在内部存储搜索序列的状态，只是为了知道从哪里开始搜索下一个 token。 我不敢说这是一个糟糕的编程实践（天啊，它是标准的 ANSI C 库！）。 我们只会更改我们 sql 版本的函数签名。 无论如何，我们不能在我们的 Transact SQL 代码中使用局部静态变量或指针技巧。
我们的 Transact SQL 版本
然后我们大肆宣传的函数看起来像这样
CREATE FUNCTION strtok(
@sStringToParse varchar(max)
,@sSeparator char(1)
,@nPosition int Output
)
RETURNS varchar(max)
CREATE PROCEDURE strtok(
@sStringToParse varchar(max)
,@sSeparator char(1)
,@sToken varchar(max) Output
,@nPosition int Output
)
但是 Transact Sql 中的函数比存储过程灵活得多。 它们的好处是可以在表达式中使用，甚至可以作为查询或其他数据操作的一部分。 记住，伙计们，我们是 C/C++ 人，只是在转录一个 C 函数，我们不能将它转换为过程，这听起来像是一种罪过。
所以让我们探索另一种解决方案。 我们的问题如下。 我们需要找到一种方法从 Transact SQL 函数返回两个值。 那么为什么不使用表值函数呢？ 它可以返回一行包含我们两个需要的值
CREATE Function strtok (
@sStringToParse varchar(max)
,@sSeparator char(1)
,@nStartPosition int
)
Returns @tbResult Table (
Token Varchar(max)
,NextStartPosition int
)
As
...
然后我们可以在循环中使用它，例如
Declare @sList Varchar(max)
Declare @sToken Varchar(max)
Declare @nPos Int
Set @sList = 'one, two, three'
Set @nPos = 1
Set @sToken = ''
While @sToken Is Not Null
Begin
Select @sToken = Token,
@nPos = NextStartPosition
From strtok(@sList, ',', @nPos)
-- do something with @sToken
End
注意，从 strtok 调用中恢复 NextStartingPosition 至关重要，以便将其作为参数 @nStartPosition 重新注入到下一次调用中。
我们还可以解析二维结构
Declare @sList Varchar(max)
Declare @sRowToken Varchar(max), @sCellToken Varchar(max)
Declare @nPosX Int, @nPosY Int
Set @sList = 'one, two, three; four, five, six; seven, eight, nine'
-- outer cycle
Set @sRowToken = ''
Set @nPosY = 1
While @sRowToken Is Not Null
Begin
Select @sRowToken = Token,
@nPosY = NextStartPosition
From strtok(@sList, ';', @nPosY)
Select @sRowToken
If @sRowToken Is Null
Break;
-- inner cycle
Set @sCellToken = ''
Set @nPosX = 1
While @sCellToken Is Not Null
Begin
Select @sCellToken = Token,
@nPosX = NextStartPosition
From strtok(@sRowToken, ',', @nPosX)
-- do something with @sRowToken
Select @sCellToken
End
End
所以这看起来是一个可行的想法。 让我们为 Transact-Sql strtok 函数提供完整的源代码
CREATE Function strtok (
@sStringToParse Varchar(max)
,@sSeparator char(1)
,@nStartPosition int
)
Returns @tbResult Table (
Token Varchar(max)
,NextStartPosition int
)
AS
Begin
Declare
@nNextPos int
,@nLenStringToParse int
,@sToken varchar(max)
Select @nLenStringToParse = Len(@sStringToParse)
-- Special case: input string is null
If @sStringToParse Is Null
Begin
Select
@nNextPos = Null
,@sToken = Null
End
-- Special case: separador is null. Return full input string
Else If @sSeparator Is Null
Begin
Select
@nNextPos = @nLenStringToParse + 1
,@sToken = Substring(@sStringToParse, @nStartPosition, @nLenStringToParse)
End
-- Special case: input string is empty.
Else If @nLenStringToParse = 0
Begin
If @nStartPosition = 1
Select
@nNextPos = 2
,@sToken = @sStringToParse
Else
Select
@nNextPos = @nStartPosition + 1
,@sToken = Null
End
-- Special case: input string ends with separator
Else If @nStartPosition = @nLenStringToParse + 1
And Substring(@sStringToParse, @nLenStringToParse, 1) = @sSeparator
Begin
Select
@nNextPos = @nStartPosition + 1
,@sToken = ''
End
-- In any other case if we are at the end of the input string, return null signalling end...
Else If @nStartPosition > @nLenStringToParse
Begin
Select
@nStartPosition = Null
,@sToken = Null
End
-- Normal cases...
Else
Begin
Select @nNextPos = CharIndex(@sSeparator, @sStringToParse, @nStartPosition)
If @nNextPos Is Null
Begin
Select
@sToken = Null
,@nNextPos = Null
End
-- Separator not found: return the full string
Else If @nNextPos = 0
Begin
If @nStartPosition = 0
Select @sToken = @sStringToParse
Else
Select @sToken = SUBSTRING( @sStringToParse, @nStartPosition, @nLenStringToParse )
Select @nNextPos = @nLenStringToParse + 1
End
Else
Begin
Select @sToken = SUBSTRING( @sStringToParse, @nStartPosition, @nNextPos - @nStartPosition )
Select @nNextPos = @nNextPos + 1
End
Set @nStartPosition = @nNextPos
End
Insert Into @tbResult (NextStartPosition, Token)
Values (@nNextPos, @sToken)
Return
End
尽情享用！
结论
我们已经成功地创建了一个 Transact-SQL 函数，用于解析包含分隔列表的字符串。
参考文献
- strtok() C++ 参考，网址为 http://www.cplusplus.com/reference/cstring/strtok/?kw=strtok