How to Append value to an existing column value

  • Ronvy

    Mr or Mrs. 500

    Points: 597

    Table - Batch

    BatchNo BatchName PublicationList

    -------- ----------- --------------

    1 Morning 23,53,23,34,36,56,67,78,232

    2 Afternoon 98,75,55,32,73,456,865

    3 Evening 5,6,8,3,778,434,43,788

    All the publications are separated by commas. So If I want to add another publication, I just need to append the already existing value by adding a comma and then the value.

    I want to add another publication 999 into Morning batch PublicationList - BatchNo 1.

    Is it possible to append the value using Insert or any other method.

    Could anyone please help me in this regard ?

    Many thanks in advance,

    Ron.

  • vadim.a.maslov

    SSC-Addicted

    Points: 462

    update Batch

    set PublicationList = PublicationList + ', 999'

    where BatchNo = 1;

  • Ronvy

    Mr or Mrs. 500

    Points: 597

    Appreciate your quick response.

    Well I would like to add only if that is not present already. If it already exists, then I won't.

    So how can I do that.

    Many thanks n Regards,

    Ron.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    May I just observe that you have a rather poor database design there and that you may want to consider normalising it. It would certainly make adding or removing a publication a great deal easier if the publications were stored in a separate table.

    Perhaps something like

    Batch (BatchNo, BatchName)

    BatchPublication (BatchNo, PublicationNo)

    That would mean that adding a publication to a batch is just an insert into the child table. You could put the primary key on the two columns, ensuring that you could not get a publication twice in the same batch.

    It would also mean that removing a publication from a batch would just be a delete statement, instead of the rather complex and messy string manipulation currently required.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • vadim.a.maslov

    SSC-Addicted

    Points: 462

    Agree with Gail 100%.

    but here is a quick solution to your problem...

  • vadim.a.maslov

    SSC-Addicted

    Points: 462

    ... hit post too soon 🙂

    if object_id('proc_update_batch') is not null drop procedure proc_update_batch;

    go

    create procedure proc_update_batch

    @batchNo int,

    @value varchar(10)

    as

    declare @check char,

    @startPos int,

    @endPos int,

    @PublicationList varchar(1000)

    set @PublicationList = (select PublicationList from batch where batchno = @batchNo)

    set @endPos = CHARINDEX ( ',', @PublicationList)

    set @startPos = 1

    while @endPos <> 0

    begin

    if substring(@PublicationList, @startPos, @endPos - @startPos) = @value

    set @check = 'Y'

    set @startPos = @endPos + 1

    set @endPos = CHARINDEX ( ',', @PublicationList, @endPos + 1)

    end

    if substring(@PublicationList, @startPos, len(@PublicationList) - @startPos+1) = @value

    set @check = 'Y'

    if isnull(@check, 'N') <> 'Y'

    begin

    begin transaction

    update Batch

    set PublicationList = PublicationList + ',' + @value

    where batchno = @batchno

    commit;

    end

    go

    exec proc_update_batch 1,'999'

    select * from batch

  • Heslous

    SSC Enthusiast

    Points: 160

    Ronvy - Friday, August 8, 2008 7:04 AM

    Table - BatchBatchNo BatchName PublicationList-------- ----------- --------------1 Morning 23,53,23,34,36,56,67,78,2322 Afternoon 98,75,55,32,73,456,8653 Evening 5,6,8,3,778,434,43,788All the publications are separated by commas. So If I want to add another publication, I just need to append the already existing value by adding a comma and then the value.I want to add another publication 999 into Morning batch PublicationList - BatchNo 1.Is it possible to append the value using Insert or any other method.Could anyone please help me in this regard ?Many thanks in advance,Ron.

    vadim.a.maslov - Friday, August 8, 2008 7:19 AM

    update Batchset PublicationList = PublicationList + ', 999'where BatchNo = 1;

    Hi Vadim...    i was looking for such possibilities and basically you showed that we can achieve that very simply... thanks a lot


    Steph

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply