Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Posts
    6

    Unanswered: updating one column from values in another column

    Hi all -

    I have a series of records - there is one field called fullname -
    its contents look like this....

    Ainsworth, James R. 693

    What i need to accomplish - is to break out the 693 from the fullname field, and put it in another field - on the same record called outcomesid

    I don't need to modify the fullnames field - but i just need to get that numeric and put it into another field, on the same record...


    I have created a function that will enumerate the values in a field - and it will return all the fields seperated by a space - here is the code for the function, and implementation....



    drop function dbo.spctostr;


    Create Function dbo.SpcToStr ( @Array varchar(1000))
    returns @IntTable table
    (
    id int IDENTITY (0, 1) NOT NULL,
    StrValue varchar(1000)
    )
    AS
    begin
    declare @separator char(1)
    set @separator = ' '
    declare @separator_position int
    declare @array_value varchar(1000)
    set @array = @array + ' '
    while patindex('% %' , @array) <> 0
    begin
    select @separator_position = patindex('% %' , @array)
    select @array_value = left(@array, @separator_position - 1)
    Insert @IntTable
    Values (Cast(@array_value as varchar(1000)))
    select @array = stuff(@array, 1, @separator_position, '')
    end
    return
    end


    Select top 1 * from dbo.SpcToStr('Barefield, Robert Bobby J. 578') order by id desc


    This line above will return the number only -


    This line below will return them all...
    Select * from dbo.SpcToStr('Barefield, Robert Bobby J. 578') order by id desc


    4 578
    3 J.
    2 Bobby
    1 Robert
    0 Barefield,



    So the question is....

    How do i loop through all the records, busting out the
    fullname field, using the function above, and populate another
    field, on the same record????


    thanks
    tony

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249

    Re: updating one column from values in another column

    --Try this

    Update "YourTable" set
    Number=substring(
    FullName,len(FullName)
    -charindex(' ' ,REVERSE(FullName))+2
    ,4000)
    ,FullName=
    rtrim(
    left(
    FullName,len(FullName)
    -len(
    substring(
    FullName,len(FullName)
    -charindex(' ' ,REVERSE(FullName))+2
    ,4000)
    )
    )
    )

  3. #3
    Join Date
    May 2002
    Posts
    6

    Talking WHOOO HOOOO



    Dude - that was perfect!!!!


    you are the MASTER!!!!



    thanks a million

    take care
    tony



Posting Permissions

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