Question

sql 中如何用select语句提取第N个"|"和第N+1个"|"中的值

例:
1081||04401453|||8038000823|||A36490|803800|720402||1|17.90|0|0|0|0|0||||0||||||||||||||||0.90|17.00||

1081|||||8055000314|||A07060|805500|702701||1|0.90|0|0|0|0|0||||0||||||||||||||||0|0.90||

要select以上几个字符串第10个"|"和第11个"|"中的数据有办法吗？如果select不行的话还有什么方法？谢谢各位!

Answer 1

asp似乎有 split函数，

values[i] = split(field,'|')

这样来获取数据

Answer 2

--建立分割函数

Create FUNCTION [dbo].[SplitToTable]
(
@SplitString nvarchar(max),
@Separator nvarchar(10)=' '
)
RETURNS @SplitStringsTable TABLE
(
[id] int identity(1,1),
[value] nvarchar(max)
)
AS
BEGIN
DECLARE @CurrentIndex int;
DECLARE @NextIndex int;
DECLARE @ReturnText nvarchar(max);
SELECT @CurrentIndex=1;
WHILE(@CurrentIndex<=len(@SplitString))
BEGIN
SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
IF(@NextIndex=0 OR @NextIndex IS NULL)
SELECT @NextIndex=len(@SplitString)+1;
SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);
SELECT @CurrentIndex=@NextIndex+1;
END
RETURN;
END

go

select * from [dbo].[SplitToTable](
'1081||04401453|||8038000823|||A36490|803800|720402||1|17.90|0|0|0|0|0||||0||||||||||||||||0.90|17.00||','|')
where id=3