SQL help on a name dilemma

Question

I have a column which has surname and firstname plus salutation in. e.g.

Bloggs,Joe,Mr

I need to break this out into Bloggs Joe Mr as 3 separate columns. Any ideas appreciated.

The other thing is I won't know how many commas are in the initial column.

Answer 1

I would do the following:

http://stackoverflow.com/questions/2312581/comma-delimited-sql-string-need-to-separated

and then insert into a table as columns

Answer 2

Possible answer: http://www.sql-server-helper.com/functions/comma-delimited-to-table.aspx

^_^

Answer 3

It depends on which DBMS you want to implement it.. here is a discussion about spliting strings on MySQL for example

Answer 4

If I'm stuck doing this to fix a badlly formed input file, I usually work from the right and do the update to the other column then delete what is from the comma to the end of the field. Then I will run this in a loop until ther are no more entries with a comma in them. Your biggest problem is this only works if all the data are in the same columns int he same order. For instance if you data can take the form of Bloggs,Mr.,Joe as well as Bloggs,Joe,Mr then you have an intepretation problem not just a SQL problem.

If you might have Bloggs,Joe,Mr and Bloggs,Joe but not Bloggs,Mr or Bloggs,Mr.,Joe then work from the left not the right.