Parsing delimited words from a column

Create table myTable (myCol varchar(50), Col1 varchar(10), Col2 varchar(10),
col3 varchar(10), col4 varchar(10), col5 varchar(10))

Insert Into myTable Values ('AA1|BBB1|CCCC1|DDD1|EEEE1', Null,Null,Null,Null,Null)
Insert Into myTable Values ('AAA2|BBB2|CC2|DDD2|EEEE2', Null,Null,Null,Null,Null)
Insert Into myTable Values ('AAAAA3|BBB3|CCCC3|DDD3|EEEE3', Null,Null,Null,Null,Null)
Insert Into myTable Values ('AAA4|BBB4|CCCC4|DD4|EEEE4', Null,Null,Null,Null,Null)
Insert Into myTable Values ('A5|BBB5|CCCC5|DDD5|E5', Null,Null,Null,Null,Null)
Insert Into myTable Values ('AAA6|BBB6|CCCC6|DDD6|EEEE6', Null,Null,Null,Null,Null)
Insert Into myTable Values ('AAA7|BBB7|CCCCC7|DDD7|EEEE7', Null,Null,Null,Null,Null)
Insert Into myTable Values ('AA8|BBBBBBB8|CCCC8|DDD8|EEEE8', Null,Null,Null,Null,Null)

declare @i1 int
declare @i2 int
declare @i3 int
declare @i4 int

update myTable
set @i1 = charindex('|', myCol),
col1 = left(myCol, @i1-1),
@i2 = charindex('|',myCol,@i1+1),
col2 = substring(myCol, @i1+1, @i2-@i1-1),
@i3 = charindex('|',myCol, @i2+1),
col3 = substring(myCol, @i2+1, @i3-@i2-1),
@i4 = charindex('|',myCol, @i3+1),
col4 = substring(myCol, @i3+1, @i4-@i3-1),
col5 = substring(myCol, @i4+1, 50)
select * from myTable

Source: https://weblogs.sqlteam.com/mladenp/2005/08/01/7421/
Viewed 17549 times