-- Code to generate the code segment below...
-- General logic ->
-- substring(@field + '|', @PrevIdx + 1, charindex('|', @field + '|', @PrevIdx + 1) - @PrevIdx - 1)
--
declare @pos int, @numtokens int, @field varchar(128), @previdx varchar(255), @maxtokens int
select @maxtokens = 8, @numtokens = 0, @field = '@Str', @previdx = '0' --  charindex('|', @str, 1)
select @field = @field + ' + ''|'''
Print 'SELECT'
while(@numtokens < @maxtokens)
begin
 print 'substring(' + @field + ', ' + case when @numtokens = 0 then '0' else @previdx end + 
        ' + 1, charindex(''|'', ' + @field + ', ' +
        case when @numtokens = 0 then '0' else @previdx end + ' + 1)' +
        ' - ' + case when @numtokens = 0 then '0' else @previdx end + ' - 1 )' +
        case when @numtokens < @maxtokens - 1 then ',' else '' end
 select @previdx = ' charindex(''|'', ' + @field +
                        case when @numtokens = 0 then '' else ', ' + @previdx + ' + 1' end + ')',
        @numtokens = @numtokens + 1
end

- Generated SELECT statement...
declare @str varchar(200)
-- Positions: '1234567890123456789012345678901234567890123456789012345678901'
select @Str = 'Metropolitan Trains|30|60|Weekday|1999-10-04|09:00:00|200|1.5'
SELECT
substring(@Str + '|', 0 + 1, charindex('|', @Str + '|', 0 + 1) - 0 - 1 ),
substring(@Str + '|',  charindex('|', @Str + '|') + 1, charindex('|', @Str + '|',
        charindex('|', @Str + '|') + 1) - charindex('|', @Str + '|') - 1 ),
substring(@Str + '|',  charindex('|', @Str + '|',  charindex('|', @Str + '|') + 1) + 1,
        charindex('|', @Str + '|',  charindex('|', @Str + '|',
        charindex('|', @Str + '|') + 1) + 1) -
        charindex('|', @Str + '|', charindex('|', @Str + '|') + 1) - 1 ),
substring(@Str + '|',  charindex('|', @Str + '|',  charindex('|', @Str + '|',
        charindex('|', @Str + '|') + 1) + 1) + 1, charindex('|', @Str + '|',
        charindex('|', @Str + '|',  charindex('|', @Str + '|',
        charindex('|', @Str + '|') + 1) + 1) + 1) -
        charindex('|', @Str + '|',  charindex('|', @Str + '|',
        charindex('|', @Str + '|') + 1) + 1) - 1 ),
substring(@Str + '|',  charindex('|', @Str + '|',  charindex('|', @Str + '|',
        charindex('|', @Str + '|',  charindex('|', @Str + '|') + 1) + 1) + 1) + 1,
        charindex('|', @Str + '|',  charindex('|', @Str + '|', charindex('|', @Str + '|',
        charindex('|', @Str + '|',  charindex('|', @Str + '|') + 1) + 1) + 1) + 1) -
        charindex('|', @Str + '|',  charindex('|', @Str + '|',charindex('|', @Str + '|',
        charindex('|', @Str + '|') + 1) + 1) + 1) - 1 ),
substring(@Str + '|',  charindex('|', @Str + '|',  charindex('|', @Str + '|',
        charindex('|', @Str + '|',  charindex('|', @Str + '|', 
        charindex('|', @Str + '|') + 1) + 1) + 1) + 1) + 1,
        charindex('|', @Str + '|',  charindex('|', @Str + '|', charindex('|', @Str + '|',
        charindex('|', @Str + '|',  charindex('|', @Str + '|',
        charindex('|', @Str + '|') + 1) + 1) + 1) + 1) + 1) -
        charindex('|', @Str + '|',  charindex('|', @Str + '|', charindex('|', @Str + '|',
        charindex('|', @Str + '|',  charindex('|', @Str + '|') + 1) + 1) + 1) + 1) - 1 ),
substring(@Str + '|',  charindex('|', @Str + '|',  charindex('|', @Str + '|',
        charindex('|', @Str + '|',  charindex('|', @Str + '|',  charindex('|', @Str + '|',
        charindex('|', @Str + '|') + 1) + 1) + 1) + 1) + 1) + 1, charindex('|', @Str + '|',
        charindex('|', @Str + '|',  charindex('|', @Str + '|',  charindex('|', @Str + '|',
        charindex('|', @Str + '|',  charindex('|', @Str + '|',
        charindex('|', @Str + '|') + 1) + 1) + 1) + 1) + 1) + 1) -  charindex('|', @Str + '|',
        charindex('|', @Str + '|',  charindex('|', @Str + '|',  charindex('|', @Str + '|',
        charindex('|', @Str + '|',  charindex('|', @Str + '|') + 1) + 1) + 1) + 1) + 1) - 1 ),
substring(@Str + '|',  charindex('|', @Str + '|',  charindex('|', @Str + '|',
        charindex('|', @Str + '|', charindex('|', @Str + '|',  charindex('|', @Str + '|',
        charindex('|', @Str + '|', charindex('|', @Str + '|') + 1) + 1) + 1) + 1) + 1) + 1) + 1,
        charindex('|', @Str + '|', charindex('|', @Str + '|',  charindex('|', @Str + '|',
        charindex('|', @Str + '|',  charindex('|', @Str + '|',
        charindex('|', @Str + '|',  charindex('|', @Str + '|',
        charindex('|', @Str + '|') + 1) + 1) + 1) + 1) + 1) + 1) + 1) -  charindex('|', @Str + '|',
        charindex('|', @Str + '|', charindex('|', @Str + '|', charindex('|', @Str + '|',
        charindex('|', @Str + '|', charindex('|', @Str + '|',
        charindex('|', @Str + '|') + 1) + 1) + 1) + 1) + 1) + 1) - 1 )

This page was last updated on May 01, 2006 04:28 PM.