How can I insert a substring of a larger string without knowing the length of the substring?

I'm working in ms-sql, and here's what I'm trying to do in pseudocode:

Suppose I have a pool of fruits, and each of these fruits is associated with a static number code, and some of the fruits have the same code:

apples = 11
bananas = 27
grapes = 34
pears = 10
watermelon = 8
figs = 34

And I have a dynamically generated string with a series of items delimited by comas, like so:

StringA: apples, bananas, grap开发者_如何学JAVAes, pears

What I want to do, is to parse each item in the string, and insert it into a separate row in a table, with the fruit going into the fruit column and the code going into the code column.

If I only know that look at StringA, either grapes or figs has been found, is there a way that I can use the substring function, or a similar function, to grab that part of the string for my insert statement, regardless of its length?

I hope this makes sense.

What I want to do is to use something like substring(@stringA, starting point of the found substring, the next coma delimiter or if there is none the end of the string) in my insert statement.

---

Not really, the very definition of a substring must include the number of characters you need as well as the starting point.

But is what you are trying to achieve something like:

select
'Apples' Name, 1 Value into #Fruit
union
select
'Pears' Name, 2 Value
union
select 'Oranges' Name, 3 Value


select 'Apples, Oranges' as Value into #Value
union
select 'Apples, Figs'
union
select 'Oranges, Figs'
union
select 'Pears, Oranges'

select * from 
#Value
left join
#Fruit on charindex(#Fruit.Name,#Value.Value) >0