SQL Server子字符串循环 - SQL Server substring loop

我需要关于子字符串的帮助。

列guild_data包含binary(4000)。

我需要从269开始，每40和15个长度得到一个值，直到最后。

示例：

SELECT DISTINCT
        CONVERT(VARCHAR,substring(guild_data, 269, 15)) AS name,
        CONVERT(VARCHAR,substring(guild_data, 309, 15)) AS name1,
        CONVERT(VARCHAR,substring(guild_data, 349, 15)) AS name2,
        CONVERT(VARCHAR,substring(guild_data, 389, 15)) AS name3,
    -- so on until it gets till the end...
FROM 
        guild_data

有什么方法可以更有效地做到这一点吗？

我估计大约有100次重复。。。

  269, 309, 349, 389, 429, 469, 509, 549, 589, 629, 669, 709, 749, 789, 829, 869, 909, 949, 989, 1029, 1069, 1109, 1149, 1189, 1229, 1269, 1309, 1349, 1389, 1429, 1469, 1509, 1549, 1589, 1629, 1669, 1709, 1749, 1789, 1829, 1869, 1909, 1949, 1989, 2029, 2069, 2109, 2149, 2189, 2229, 2269, 2309, 2349, 2389, 2429, 2469, 2509, 2549, 2589, 2629, 2669, 2709, 2749, 2789, 2829, 2869, 2909, 2949, 2989, 3029, 3069, 3109, 3149, 3189, 3229, 3269, 3309, 3349, 3389, 3429, 3469, 3509, 3549, 3589, 3629, 3669, 3709, 3749, 3789, 3829, 3869, 3909, 3949, 3989

谢谢。

我正在查找T-SQL查询。不过，我将在PHP脚本中使用它。

----编辑----我们把这个剧本放在这里，它也行。但是太长了：）

CONVERT(VARCHAR,substring(guild_data, 269, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 309, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 349, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 389, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 429, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 469, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 509, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 549, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 589, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 629, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 669, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 709, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 749, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 789, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 829, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 869, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 909, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 949, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 989, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 1029, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 1069, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 1109, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 1149, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 1189, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 1229, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 1269, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 1309, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 1349, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 1389, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 1429, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 1469, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 1509, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 1549, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 1589, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 1629, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 1669, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 1709, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 1749, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 1789, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 1829, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 1869, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 1909, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 1949, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 1989, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 2029, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 2069, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 2109, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 2149, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 2189, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 2229, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 2269, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 2309, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 2349, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 2389, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 2429, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 2469, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 2509, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 2549, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 2589, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 2629, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 2669, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 2709, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 2749, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 2789, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 2829, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 2869, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 2909, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 2949, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 2989, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 3029, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 3069, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 3109, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 3149, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 3189, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 3229, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 3269, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 3309, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 3349, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 3389, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 3429, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 3469, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 3509, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 3549, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 3589, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 3629, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 3669, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 3709, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 3749, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 3789, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 3829, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 3869, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 3909, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 3949, 15)) AS glname,
CONVERT(VARCHAR,substring(guild_data, 3989, 15)) AS glname

以下是CTE使用示例：

DECLARE @String VARCHAR(4000) = '111111111111111222222222222222333333333333333444444444444444555555555555555'
DECLARE @b VARBINARY(4000) = CONVERT(varbinary(4000),@string)
DECLARE @StartPos int=1
DECLARE @Len tinyint=15
;WITH C (Orig,Startpos,Value) AS (
SELECT @b,@StartPos,CONVERT(VARCHAR,SUBSTRING(@b,@StartPos,@Len))
UNION ALL
SELECT @b,C.Startpos+@Len,CONVERT(VARCHAR,SUBSTRING(@b,C.StartPos+@Len,@Len)) FROM C
WHERE C.Startpos+@Len < = LEN(@b)
)
select C.Value from c

结果：

Value
111111111111111
222222222222222
333333333333333
444444444444444
555555555555555

在您的情况下，您应该将@StartPos更改为269，将@Len更改为15

另一种方法

declare @string varchar(4000)
set @string = '111111111111111222222222222222333333333333333444444444444444555555555555555' 
declare @startpos int=1 
declare @len tinyint=15 
declare @t table(data varchar(100))
while len(@string)>15
begin
    insert into @t
    select substring(@string,@startpos,@len)
    select @string=substring(@string,@startpos+15,len(@string))
end
    insert into @t
    select substring(@string,@startpos,@len)
select data from @t