Extracting data from a table

[newomoge]

Dear all, I have a table which has a column that contains data like '
'654:JT12;678:JT56;'
It is sometimes more or less but it is always in the pattern shown
I am trying to extract the item between the ';' and the ':'
(In other words, I am trying to extract 'JT12' and 'JT56' and store it in another table).
Can anyone be kind enough to help me out? I tried Patindex and substring but I can't seem to be able to construct a usable function that will strip out what I need

Any help will be highly appreciated
Nneka

[nosepicker]

There are several ways in which to do this. Just so I understand you correctly, did you say that you could also have data with more or less data elements, like this?

'654:JT12;678:JT56;'
'654:JT12;'
'654:JT12;678:JT56;123:JT99;456:JT88'

If yes, then do you have a lot of data like this, or does this only happen once in a while within your table?

[newomoge]

Hi,
Thanks for your rapid response.
Yes, it could change, It can sometimes have data with more or less data elements and sometimes it can be null and the data is always different but it will always have the JT part delimited by the colon and semi colon.

The table for example has 3 colunms. One of the columns contains the data in question.
so sometimes in a record it can be null. (see below)

'654:JT12;678:JT56;'
null
'654:JT12;'
'654:JT12;678:JT56;123:JT99;456:JT88'
null

I am only tring to extract the 'JT' (i.e JT12) part of the data using SQL and store it in another table.
Thanks again
Any help will be highly appreciated.