-- 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