How to make a line/string to multiple records So, I want to create a teacher-degree (1:m) table. I have this example:

One professor has 3 degrees.

This is the actual string I have(multiple of these actually):


  B.A, 1983, University of China; M.B.A, 1999, University of Taiwan;
  Ph.D, 1999, University of Southern California,


The fields are: professorID, degreeType, degreeYear, Institute

The expected table data:


  Prof-13434, B.A, 1983, University of China
  
  Prof-13434, M.B.A, 1999, University of Taiwan
  
  Prof-13434, Ph.D, 2003, University of  California


That is my goal, I have tried everything but I cant figure anything out. Trying to go from one string to multiple records is harder than I thought.
Just to be clear:

Each degree has:

Position 1) Degree Type
Position 2) Year
Position 3) Univerity / College

Item 4) There can be more than 1 degree or only 1 degree.

Other Criteria:  Each entire section is separated by a ';' (semicolon) each subsection is separated by a comma.
No other commas are in the string?

Here's the code.  

Declare @TestName as varchar(500)
Declare @Pos1 as int
Declare @Pos2 as int
Declare @Pos3 as int
Declare @Pos4 as int

Set @TestName = 'B.A, 1983, University of China; M.B.A, 1999, University of Taiwan; Ph.D, 1999, University of Southern California,'

---Start loop
--Get the position of the first 'comma'
Select @Pos1= PATINDEX('%,%',@TestName)

--Get the position of the second 'comma'
Select @Pos2= PATINDEX('%,%',Substring(@TestName, (@Pos1+1), (Len(@TestName)-(@Pos1+1))))

--Get the position of the end section.
Select @Pos3= PATINDEX('%;%',Substring(@TestName, (@Pos2+@Pos1+1), (Len(@TestName) )))

Select 
SUBSTRING(@TestName, 1, @Pos1-1) as DegreeType, 
lTRIM(SUBSTRING(@TestName, @Pos1+1, @Pos2-1)) as GradYear,
SUBSTRING(@TestName, @Pos1+@Pos2+1, @Pos3-1) as University

-- Reset the value of @TestName so you can loop start over.
Set @TestName = SUBSTRING(@TestName,@Pos1+@Pos2+@Pos3+1, Len(@TestName) - @Pos3+1)

--Shows you what is left.
Select @TestName
--Loop End


You'll still need to build loop and the above code is in SQL Server but it should translate across if you are using another database system.