问题描述
我有一个字符串:
@TempCol = sigma_x1,sigma_x2,...,sigma_xd,XX,YY,ZZ
@TempCol = sigma_x1,sigma_x2,...,sigma_xd,XX,YY,ZZ
那么我怎样才能获得该字符串的特定部分,比如一个索引.所以
So how could I get a specific part of that string, based on, lets say an index. so
- 如果索引为 0,则获取 sigma_x1
- 如果索引为 1,则获取 sigma_x2
- 如果索引为 2,则获取 sigma_x3
- 如果索引是 d-1,得到 sigma_xd
- 如果索引是 d,得到 XX,
- 如果索引是d+1,得到YY等等.
以前Andriy M 解决了一个类似的问题,他的代码根据一个数字获取一个子字符串,但通过以下方式返回一个子字符串:
Previously Andriy M solved a similar issue, his code gets a substring based on a nomber but returns a substring the following way:
- 如果@d 是 1,得到 sigma_x1
- 如果@d 是 2,得到 sigma_x1,sigma_x2
- 如果@d 是 3,得到 sigma_x1,sigma_x2,sigma_x3
- 如果@d 是 4,得到 sigma_x1,sigma_x2,sigma_x3,sigma_x4
- 如果@d 是 d,得到 sigma_x1,sigma_x2,sigma_x3,sigma_x4,...,sigma_xd(所有字符串)
如何更新此程序以获取特定元素?
How to update this procedure to get specific element?
DECLARE @TempCol varchar(max), @d int, @p int, @Result varchar(max);
SET @TempCol = 'item1,item2,itemA,itemB,item#,item$';
SET @d = 3;
SET @p = 1;
WHILE @d > 0 AND @p > 0 BEGIN
SET @p = CHARINDEX(',', @TempCol, @p);
IF @p > 0 SET @p = @p + 1;
SET @d = @d - 1;
END;
IF @p = 0
SET @Result = @TempCol
ELSE
SET @Result = SUBSTRING(@TempCol, 1, @p - 2);
SELECT @Result;
推荐答案
试试这个.希望这能满足您的需求.
just try this. hope this will meet your needs.
创建一个函数GetIndex
,它接受字符串和分隔符来分割字符串
create a function GetIndex
, which accepts string and delimiter to split the string
CREATE FUNCTION dbo.GetIndex(@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (itemindex int identity(1,1), items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
你可以查询,
假设你需要第四个索引
and you can query like,
suppose you need 4th index then
select * from dbo.GetIndex(@TempCol,',') where itemindex = 4
然后获取第 4 个索引的项目
to get an item of 4th index then
select items from dbo.GetIndex(@TempCol,',') where itemindex = 4
获取项目到变量
select @Aux = items from dbo.GetIndex(@TempCol,',') where itemindex = 4
这篇关于通过索引获取字符串的特定部分?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!