Help Needed

Hi,
I need to insert records in a table from a varchar(4000) variable, this variable will hold the values in below format
id|name:id|name:...
And the table structure is
Create table my_table(
ID int,
Name Varchar(50))
In my variable Pipe (|) will seperate the id from name and Colon ) will seperate the records.
And the expected number of records vary from 1 to 20.
I tried using substring & charindex but some hoe not able to get what I was looking for.
Can any body help me in implementing this.

I don't think there is an alternative to parsing the string. If you show us the code that you have so far, we'd be happy to take a look at what might be going wrong.

Below is the code I'm using for my testing. I need to add this code in my existing procedure and this variable @vendor will be a varchar(4000) field. And then in my procedure I need to insert this into a table. with just two fields
DECLARE @COUNTER int, @VENDOR varchar(4000)
SET @COUNTER = 0
SET @VENDOR = '1234|ABCD:2345|BCDE'
--SELECT @COUNTER = CHARINDEX('|', @VENDOR, @COUNTER)
--SELECT @COUNTER
SELECT SUBSTRING(@VENDOR, @COUNTER, CHARINDEX('|', @VENDOR, @COUNTER)) AS ID
--SELECT @COUNTER = CHARINDEX('|', @VENDOR, @COUNTER)
--SELECT @COUNTER
--SELECT CHARINDEX(':', @VENDOR, @COUNTER) - @COUNTER - 1
SELECT SUBSTRING(@VENDOR, @COUNTER + 1, CHARINDEX(':', @VENDOR, @COUNTER) - @COUNTER - 1) AS NAME

(1) Find the first | delimiter in the string, store position in @Counter.
(2) Use the LEFT function with @Counter (minus 1) to copy 1234 into a new variable, @ID.
(3) Use SUBSTRING with @Counter to trim off everything including the first | delimiter in @Vendor.
(4) Find the first : delimiter remaining string in @Vendor, store position in @Counter.
(5) Use the LEFT function with @Counter (minus 1) to copy ABCD into a new variable, @Name.
(6) INSERT INTO MyTable (ID, Name) VALUES (@ID, @Name)
(7) Use SUBSTRING to trim off everything including the first : delimiter in @Vendor.
Repeat steps 1-7 until @Counter = 0 for the : delimiter, and make sure you insert the last set of items.

This worked fine....
Thanks a tonne for your help........!!!!