SQL Server Performance

Help Needed

Discussion in 'SQL Server 2005 General Developer Questions' started by rohit2900, Dec 15, 2008.

  1. rohit2900 Member

    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.
  2. Adriaan New Member

    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.
  3. rohit2900 Member

    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

  4. Adriaan New Member

    (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.
  5. rohit2900 Member

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

Share This Page