sql - 选择带有 charindex 问题

Question

我正在尝试使用“a”作为分隔符将此字符串分成不同的列

005031812180200000100aRemaining Inside Terminala Cassette 1(BIN1)/c:HTG 21,000a Cassette 2(BIN2)/c:HTG 23,000a Cassette 3(BIN3)/d:HTG 47,000a Cassette 4(BIN4)/d:HTG 48,000a Total : HTG 139,000aaaad

我正在用这段代码创建一个对象:

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([cassette_1] [nvarchar] (500),
                [cassette_2] [nvarchar] (500),
                [cassette_3] [nvarchar] (500),
                [cassette_4] [nvarchar] (500),
                [total] [nvarchar] (500)
               )
AS
BEGIN

 DECLARE @cassette_1 NVARCHAR(255)
 DECLARE @cassette_2 NVARCHAR(255)
 DECLARE @cassette_3 NVARCHAR(255)
 DECLARE @cassette_4 NVARCHAR(255)
 DECLARE @total NVARCHAR(255)
 DECLARE @pos INT

WHILE CHARINDEX('a ', @stringToSplit) > 0
 BEGIN
  SELECT @pos = CHARINDEX('a ', @stringToSplit)  
  SELECT @cassette_1 = SUBSTRING(@stringToSplit, 1, @pos-1)
  SELECT @cassette_2 = SUBSTRING(@stringToSplit, 1, @pos-1)
  SELECT @cassette_3 = SUBSTRING(@stringToSplit, 1, @pos-1)
  SELECT @cassette_4 = SUBSTRING(@stringToSplit, 1, @pos-1)
  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

INSERT INTO @returnList (cassette_1)
SELECT @cassette_1
INSERT INTO @returnList (cassette_2)
SELECT @cassette_2
INSERT INTO @returnList (cassette_3)
SELECT @cassette_2
INSERT INTO @returnList (cassette_4)
SELECT @cassette_4
INSERT INTO @returnList (total)
SELECT @stringToSplit

 RETURN
END    

这是我调用对象的代码:

SELECT * FROM dbo.splitstring('005031812180200000100aRemaining Inside Terminala Cassette 1(BIN1)/c:HTG 21,000a Cassette 2(BIN2)/c:HTG 23,000a Cassette 3(BIN3)/d:HTG 47,000a Cassette 4(BIN4)/d:HTG 48,000a Total : HTG 139,000aaaad')

字符串将调用表中的字符串。

这是立即获取的结果: http://sqlfiddle.com/#!18/60eb5/2

我需要一个注册表中的所有内容(我知道这是一个新的插入)但我似乎没有完成它，而且我只得到每一列中的最后一部分(磁带 4)

Answer 1

在循环内，您在每次迭代中设置所有 @casette_n 变量。要么将循环展开为四组语句，要么根据计数器获取正确的变量并为其赋值。我在下面的代码中选择了第一种可能性。

最后的INSERT可以写成一个SELECT。所以只插入一行。

并且开头似乎有一些带有名称或其他内容的标题。你也必须解决这个问题。我也在输出中添加了它，但如果你不想要它，你可以丢弃它。但在搜索第一个盒式磁带之前，必须将其从字符串中删除。

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([name] [nvarchar] (500),
                    [cassette_1] [nvarchar] (500),
                    [cassette_2] [nvarchar] (500),
                    [cassette_3] [nvarchar] (500),
                    [cassette_4] [nvarchar] (500),
                    [total] [nvarchar] (500)
                   )
AS
BEGIN
 DECLARE @name NVARCHAR(255)
 DECLARE @cassette_1 NVARCHAR(255)
 DECLARE @cassette_2 NVARCHAR(255)
 DECLARE @cassette_3 NVARCHAR(255)
 DECLARE @cassette_4 NVARCHAR(255)
 DECLARE @total NVARCHAR(255)
 DECLARE @pos INT

 -- getting the name
 SELECT @pos = CHARINDEX('a ', @stringToSplit)
 SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
 SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)

 -- getting 1st cassette
 SELECT @pos = CHARINDEX('a ', @stringToSplit)
 SELECT @cassette_1 = SUBSTRING(@stringToSplit, 1, @pos-1)
 SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)

 -- getting 2nd cassette
 SELECT @pos = CHARINDEX('a ', @stringToSplit)
 SELECT @cassette_2 = SUBSTRING(@stringToSplit, 1, @pos-1)
 SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)

 -- getting 3rd cassette
 SELECT @pos = CHARINDEX('a ', @stringToSplit)
 SELECT @cassette_3 = SUBSTRING(@stringToSplit, 1, @pos-1)
 SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)

 -- getting 4th cassette
 SELECT @pos = CHARINDEX('a ', @stringToSplit)
 SELECT @cassette_4 = SUBSTRING(@stringToSplit, 1, @pos-1)
 SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)

 -- inserting one row
 INSERT INTO @returnList (name, cassette_1, cassette_2, cassette_3, cassette_4, total)
 SELECT @name, @cassette_1, @cassette_2, @cassette_3, @cassette_4, @stringToSplit

 RETURN
END

但这只有在恰好有四个盒式磁带的情况下才有效!如果还必须处理这样的字符串，您可能需要重新考虑对于任意数量的盒式磁带。

当然，如果 'a ' 出现在数据中的某处，比如在那个名称标题中，你就完蛋了，说 'It's a wonderful day'。