Split one column into three

  • Thread starter Thread starter hhammash
  • Start date Start date
H

hhammash

Guest
Hi all,

I have a query that has 3 fields, the first field has date separated by a comma, example:
IBM,MC,HP
Microsoft,Oracle,Lotus

how can I divide this column into three using the query.

Thank you all
Hisham
 
Assuming you've got two comma's use this:

SELECT Left(Supplier,InStr(Supplier,",")-1) AS First,
Left(Mid(Supplier, InStr(Supplier,",")+1),InStr(Mid(Supplier, InStr(Supplier,",")+1),",")-1) AS Second,
Mid(Mid(Supplier, InStr(Supplier,",")+1), InStr(Mid(Supplier, InStr(Supplier,",")+1),",")+1) As Third
FROM tblSupplier;

Replace Supplier by your column name, replace tblSupplier by your table name.

BTW, looks like you're storing concatenated data.....
Why don't you store IBM,MC,HP in separate rows...

IBM
MC
HP

instead of

IBM, MC, HP ??

RV
 
Hi RV,

Thank you for your reply.

You are right, they should be in different filed. The problem is that I have taken the database this way from a previous colleague.

I will try the query and get back to you.


Best regards
Hisham
 
Thanks RV,

It is working perfectly. But I had an Error in one record because it had only two companies and one comma:
Microsoft,Dell.

Is it possible to keep it open? I mean if there are 3 commas, 2 commas 5 commas ...etc.

It was a briliant solution.

Can you please explain to me what is InStr does (does it mean In String) and the +1.

Thank a lot
Hisham
 

Users who are viewing this thread

Back
Top Bottom