Split Column by Pipe (Excel Text to Columns)

[moone009]

Example data in column1
2|3|3|3GES|5|3
|3|334|3ere||343

I would like to split this by pipes so I can update the corresponding other blank 6 columns in each row.

Example Col1 Col2 Col3 Col4 Col5 Col6 Col7
2|3|3|3GES|5|3 2 3 3 3GES 5 3

I hope this makes sense! Any help is greatly appreciated.

[rch1231]

It is easy in Excel. Select the column with the data in it and make sure that the columns to the right of it are empty. Then under Data use the option for text to columns, select delimited text and select other and put the pipe for the delimiter. If you have empty columns make sure that the box is unchecked for "Treat multiple delimiters as one"

[moone009]

Thank you I understand how to do this in excel but I wish to do this in SQL.

[rch1231]

I would do it before it gets to sql if I were you with perl (Linux or Windows) or VB (Windows). Perl has a split function that will parse the data based on a delimiter you supply. VB could do it several ways also. You can then use PERL or VB to import the data straight into the database.

[Tess James]

Make use of the string functions SUBSTRING() and CHARINDEX().

[M.Waqas Aslam]

you can use replace like this

update table
set column1 = replace(column1, '|', ' ')

May be this will solve your prob.
Regards