Here's the code to generate a SELECT and an UPDATE to "split" a fixed number of fields.
Note that this generates "blank string" values where there is a trailing delimiter and NO following value, and NULL values if a trailing delimiter (and its value, obviously!) are missing
-- Based on code by http://www.umachandar.com/technical/SQL70Scripts/Main10.htm
-- Modified by Kristen 2004/2005
DECLARE @pos int,
@numtokens int,
@field varchar(128),
@field2 varchar(128),
@strDelimiter varchar(1),
@previdx varchar(255),
@maxtokens int
-- Set these parameters as appropriate, and run down to "TEST RIG"
SELECT @maxtokens = 5, -- Number of tokens, per row, to be split
@field = 'MyCol', -- Name of Column to split
@strDelimiter = '|' -- Delimiter - e.g. CHAR(9) or '-'
SELECT @field2 = @field + ' + ''' + @strDelimiter + '''' -- Shorthand! for @field plus delimiter
-- SELECT stuff
SELECT @numtokens = 0, -- Initialisation
@previdx = '0' -- charindex('|', @str, 1)
PRINT 'SELECT'
WHILE(@numtokens < @maxtokens)
BEGIN
PRINT CASE WHEN @numtokens = 0 THEN ' ' ELSE ', ' END
+ '[COLUMN_' + CONVERT(varchar(20), @numtokens+1) + '] = '
+ 'substring(' + @field2 + ', ' + CASE WHEN @numtokens = 0 THEN '0' ELSE @previdx END
+ ' + 1, charindex(''' + @strDelimiter + ''', ' + @field2 + ', '
+ CASE WHEN @numtokens = 0 THEN '0' ELSE @previdx END + ' + 1)'
+ ' - ' + CASE WHEN @numtokens = 0 THEN '0' ELSE @previdx END + ' - 1 )'
SELECT @previdx = ' charindex(''' + @strDelimiter + ''', ' + @field2
+ CASE WHEN @numtokens = 0 THEN '' ELSE ', ' + @previdx + ' + 1' END + ')',
@numtokens = @numtokens + 1
END
PRINT 'FROM dbo.MyTable'
-- UPDATE stuff
SELECT @numtokens = 0 -- Initialisation
PRINT 'UPDATE U'
PRINT 'SET'
WHILE(@numtokens < @maxtokens)
BEGIN
IF @numtokens = 0
BEGIN
PRINT CHAR(9) + ' @I' + CONVERT(varchar(20), @numtokens+1)
+ ' = CHARINDEX(''' + @strDelimiter + ''', ' + @field2 + ')'
PRINT CHAR(9) + ', [COLUMN_' + CONVERT(varchar(20), @numtokens+1)
+ '] = LEFT(' + @field + ', @I'
+ CONVERT(varchar(20), @numtokens+1) + '-1)'
END
ELSE
BEGIN
PRINT CHAR(9) + ', @I' + CONVERT(varchar(20), @numtokens+1)
+ ' = NullIf(CHARINDEX(''' + @strDelimiter + ''', '
+ @field2 + ', @I' + CONVERT(varchar(20), @numtokens)
+ '+1), 0)'
PRINT CHAR(9) + ', [COLUMN_' + CONVERT(varchar(20), @numtokens+1)
+ '] = SUBSTRING(' + @field + ', @I' + CONVERT(varchar(20), @numtokens)
+ '+1, @I' + CONVERT(varchar(20), @numtokens+1) + '-@I'
+ CONVERT(varchar(20), @numtokens) + '-1)'
END
SELECT @numtokens = @numtokens + 1
END
PRINT 'FROM dbo.MyTable AS U'
-- TEST RIG:
DECLARE @MyTable TABLE
(
MyCol varchar(50),
MyCol1 varchar(10),
MyCol2 varchar(10),
MyCol3 varchar(10),
MyCol4 varchar(10),
MyCol5 varchar(10)
)
INSERT INTO @myTable
SELECT 'AA1|BBB1|CCCC1|DDD1|EEEE1', Null,Null,Null,Null,Null UNION ALL
SELECT 'AAA2|BBB2|CC2|DDD2|EEEE2', Null,Null,Null,Null,Null UNION ALL
SELECT 'AAAAA3|BBB3|CCCC3|DDD3|EEEE3', Null,Null,Null,Null,Null UNION ALL
SELECT 'AAA4|BBB4|CCCC4|DD4|EEEE4', Null,Null,Null,Null,Null UNION ALL
SELECT 'A5|BBB5|CCCC5|DDD5|E5', Null,Null,Null,Null,Null UNION ALL
SELECT 'AAA6|BBB6|CCCC6|DDD6|EEEE6', Null,Null,Null,Null,Null UNION ALL
SELECT 'AAA7|BBB7|CCCCC7|DDD7|EEEE7', Null,Null,Null,Null,Null UNION ALL
SELECT 'AA8|BBBBBBB8|CCCC8|DDD8|EEEE8', Null,Null,Null,Null,Null UNION ALL
SELECT 'AA9|BBBBBBB9|CCCC9|DDD9|', Null,Null,Null,Null,Null UNION ALL
SELECT 'AAA|BBBBBBBA|CCCCA|DDDA', Null,Null,Null,Null,Null UNION ALL
SELECT 'AAB|BBBBBBBB|CCCCB|', Null,Null,Null,Null,Null UNION ALL
SELECT 'AAC|BBBBBBBC|CCCCC', Null,Null,Null,Null,Null UNION ALL
SELECT 'AAD|BBBBBBBD|', Null,Null,Null,Null,Null UNION ALL
SELECT 'AAE|BBBBBBBE', Null,Null,Null,Null,Null UNION ALL
SELECT 'AAF|', Null,Null,Null,Null,Null UNION ALL
SELECT 'AAG', Null,Null,Null,Null,Null
DECLARE @I1 int,
@I2 int,
@I3 int,
@I4 int,
@I5 int
UPDATE U
SET
@I1 = CHARINDEX('|', MyCol + '|')
, [MyCol1] = LEFT(MyCol, @I1-1)
, @I2 = NullIf(CHARINDEX('|', MyCol + '|', @I1+1), 0)
, [MyCol2] = SUBSTRING(MyCol, @I1+1, @I2-@I1-1)
, @I3 = NullIf(CHARINDEX('|', MyCol + '|', @I2+1), 0)
, [MyCol3] = SUBSTRING(MyCol, @I2+1, @I3-@I2-1)
, @I4 = NullIf(CHARINDEX('|', MyCol + '|', @I3+1), 0)
, [MyCol4] = SUBSTRING(MyCol, @I3+1, @I4-@I3-1)
, @I5 = NullIf(CHARINDEX('|', MyCol + '|', @I4+1), 0)
, [MyCol5] = SUBSTRING(MyCol, @I4+1, @I5-@I4-1)
FROM @MyTable AS U
SELECT *
FROM @MyTable
Kristen