How to split data into separate columns?

[casiansala]

Hi, I am new to sql ,but i haven't give up....yet
I have one column with various information separated by ";" I would like to separate this information into separate fields? I know it can be done,but i do not know how...any sugestions?

 

[JarlH]

It's not a great idea to store data that way...

ANSI SQL has no simple way to accomplish what you want. Check out SUBSTRING and POSITION, e.g. something like:

SELECT SUBSTRING(column FROM 1 FOR POSITION(';' IN column)),
...
FROM yourtable


My suggestion is to export the table data and add some new columns. Then you import the data again, this time into separate columns.

 

[Golom]

faq701-6293 is written for Access SQL but it will probably work with ANSI syntax as well. You will need to change the comma-delimiters (",") to semicolon-delimiters (";") to apply it to your situation.

It is however somewhat complex and I must agree with JarlH that using multi-valued fields is not recommended and it breaks normalization rules about values being atomic.

 

[Signit]

What DB platform are you using SQL Server, Oracle etc.? Depending on the platform there are a variety of solutions available for this particular problem.