How to Append value to an existing column value
-
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.
-
update Batch
set PublicationList = PublicationList + ', 999'
where BatchNo = 1;
-
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.
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Agree with Gail 100%.
but here is a quick solution to your problem...
-
... 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
- Ronvy - Friday, August 8, 2008 7:04 AMvadim.a.maslov - Friday, August 8, 2008 7:19 AM
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