Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Posts
    7

    Unanswered: String Manipulation

    I have a stored procedure where i am getting values from two fields which are comma delimited. I need to read each value and insert it into database. The two fields will have the exact number of entries. However i dont know the length of the codes or quantity and the only thing i have is the comma. I wrote this while loop but it gets stuck in the first loop. The problem is that i cannot delete the string values as i enter them .Any help would be great. Thanks in advance






    DECLARE @Length int
    DecLARE @Position int
    Declare @Length1 int
    Declare @Position1 int
    Declare @SuppCode varchar(100)
    Declare @SuppQTY varchar(100)
    DECLare @SuppCode_Temp varchar(100)
    Declare @SuppCode_1 varchar(100)
    Declare @SuppQTY_TEMP varchar(100)
    Declare @SuppQTY_1 varchar(100)
    Declare @HCPC varchar(100)
    Declare @QTY varchar(100)
    DEClare @TEmp varchar(100)

    Set @SuppCode='j909,k987,l765'
    Set @SuppQTY='1,3,2'


    While len(Ltrim(@SuppCode)) > 0
    Begin
    Set @Position = CharIndex (',',@SuppCode)
    Set @length=len(@SuppCode)
    if @position=0
    Set @position=@length+1
    set @SuppCode_Temp=left(@SuppCode, @position-1)
    Set @SuppCode_1=left(@SuppCode,@Position)
    set @HCPC=@SuppCode_Temp
    Set @Position1=CharIndex(',',@SuppQTY)
    Set @length=len(@SuppQTY)
    if @position1=0
    Set @position1=@length1+1
    set @SuppQTY_Temp=left(@SuppQTY, @position1-1)
    Set @SUPPQTY_1=left(@SuppQTY,@Position1)
    set @QTY = @SuppQty_Temp
    if @HCPC is not null and @HCPC <> ''
    begin
    insert into mp_t_patient_ledger
    (date_of_entry,patient_id,ledger_type_id,practice_ id,provider_id,date_of_service_from,procedure_code ,item_quantity,diagnosis_code_1,Place_of_service_I d)
    values (getDate(),3810,2,2,2,10/10/2000 ,@HCPC, @QTY, 724,123)
    end

    --Select Replace (@SuppQty,@SuppQty_1,'')
    Select STUFF(@SuppQty,1,len(@SuppQty_1),'')
    --Select Replace (@SuppCode,@SuppCode_1,'')
    Select STUFF(@SuppCode,1,len(@SuppCode_1),'')
    End

  2. #2
    Join Date
    Sep 2003
    Posts
    212
    y dont u use dts package to load the comma delimited column values to insert into a temp table and then from there insert into ur database from the temp table?

  3. #3
    Join Date
    Dec 2003
    Posts
    7
    I actually put them in a temp table as a comma delimited field. from there i have to take each one and insert it as a row with other information in another table. What is dts package? i am fairly new at sql programming

  4. #4
    Join Date
    Sep 2003
    Posts
    212
    A dts package is a feature in sql server 2000/7.0 that performs differents tasks like bulk inserts, sql task, etc.. very helpful....
    so dts package can transform the comma delimited values into a table onyl with a few clicks (if u know what u r doin)....

    but yeah if u using sql server i highly recommend u shud look into that!!

  5. #5
    Join Date
    Dec 2003
    Posts
    7
    Now i know what your talking about. Well the values are coming in from a web page. so i cant use dts. Also they're already put in the table with other information. I need a string manipulation technique to go through them using some looping

  6. #6
    Join Date
    Sep 2003
    Posts
    212
    well parsing string is much slower..

    where r u gettin these values coming from? a webpage? what does that mean?

  7. #7
    Join Date
    Dec 2003
    Posts
    7
    I am getting these values being put in from a webpage. I already have that working and its putting these values into a temp table. now from there i have to put various values into various tables. I have only two columns where these comma delimited values come in and i need to be able to put them row at a time... So lets say if i had 1,2,3....They would go 1 in one row, 2 in another and 3 in another. I donot know the size of each item so the only thing i have to go off is the comma. So i need a loop which parses through the above string and gives me 1 and 2 and 3...as individual values through the loop... Look at the procedure it'll make more send

  8. #8
    Join Date
    Dec 2003
    Posts
    7
    it doesnt that stuff argument is not working...thats why its posted for somebody better than me at this to look at and tell me where the problem is

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    CREATE TABLE myWorkTable (Col1 int IDENTITY(1,1) NOT NULL, Col2 datetime NOT NULL, Col3 varchar(4000))
    GO
    
    
    DECLARE @string varchar(4000)
    SELECT @string = 'a,b,c,d,e,f,g,h,i,k,j'
    SET NOCOUNT ON
    DECLARE @x int, @y int, @z varchar(4000), @dt datetime
    SELECT @x = 1, @y = CHARINDEX(',',@String,@x), @dt = GetDate()
    WHILE @y <> 0
    	BEGIN
    		SELECT @z = SUBSTRING(@string,@x,@y-@x)
    		INSERT INTO myWorkTable (Col2,Col3) SELECT @dt, @z
    		SELECT @x = @y + 1
    		SELECT @y = CHARINDEX(',',@String,@x)
     	END
    
    SELECT @z = REVERSE(SUBSTRING(REVERSE(@string),1,CHARINDEX(',',REVERSE(@string))-1))
    INSERT INTO myWorkTable (Col2,Col3) SELECT @dt, @z		
    SET NOCOUNT OFF
    		
    SELECT * FROM myWorkTable
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •