Concatenate Strings of same column
-
hi All,
my requirement is as below:
contents reside in the following manner:
Node Content
12 This is somple text
12 Extra Text here add this text too
Output: This is sample text Extra Text here add this text too
i've written the following SP for this purpose. But the problem is that the varchar variable does not hold more than 8000 characters. is there any way in which i can concatenate strings as one big huge string?
delete from tempContent
declare @nodeid integer
declare curs CURSOR for
select distinct Node_Id from ivs_generalcontent where Content like '%testing%' or content like '%functional%'
open curs
fetch next from curs into @nodeid
while @@fetch_status = 0
begin
declare @temp varchar(8000)
set @temp=''
insert into tempContent
select @nodeid, ' '
declare cursContent CURSOR for
select Content from ivs_generalcontent where Node_Id = @nodeid and (Content like '%testing%' or content like '%functional%')
open cursContent
fetch next from cursContent into @temp
while @@fetch_status=0
begin
update tempContent
set Content = @temp
where nodeid = @nodeid
set @temp=' '
fetch next from cursContent into @temp
end
close cursContent
deallocate cursContent
fetch next from curs into @nodeid
end
close curs
deallocate curs
Anyone with suggestions/ideas please post a reply. Thanks
-
if you need more than 8000 characters I would suggest doing the work on the client because it's gonna be easier there than working with a text variable on the server.
ALSO you can avoid using a cursor for this task. Here's an exemple of a function that does exactly what you are trying to do :
CREATE FUNCTION [dbo].[fnFacturationPiecesBT] (@PkBT as int)
RETURNS varchar(4000) AS
BEGIN
/*
Formate toutes les pièces du bon de travail @PkBT pour la facturation et retourne le tout sur une seule ligne
Exemple pour une pièce d'un bon de travail
,ITEM="12692...........,GARNITURE 11 po.X 15 po.X 1 1/4 po. TOPOG-E, 2 , 49.5 , 99 ,31010,1,1"
,ITEM = ", , , , , , , "
*/
Declare @Items as varchar(4000)
Declare @GrandLivre as char(5)
SET @GrandLivre = '31010'-- MATÉRIEL VENTE
SET @Items = ''-- assure que la fonction ne retourne pas NULL
SELECT @Items = @Items -- concatène tous les items dans la variable @Items
+ ',ITEM="' + cast([N° de pièce] as varchar(10))--
+ ',' + dbo.fnFacturationReplaceChars(Description)-- * ne peut pas contenir le caractère ' " '
+ ',' + cast(([Qtée sortie] - [Qtée retour]) as varchar(10))--
+ ',' + cast(ISNULL([Prix vente], 0) as varchar(10))--
+ ',' + cast(([Qtée sortie] - [Qtée retour]) * ISNULL([Prix vente], 0) as varchar(20))--
+ ',' + @GrandLivre + ',1,1"'-- , ,
FROM dbo.Pièces
WHERE [N° Bon de travail] = @PkBT
AND NOT ([Qtée sortie] - [Qtée retour] = 0)
Return @Items
END
Feel free to post any question if you don't understand the code.
-
hi. Thanks for the Reply.
Currently, im concatenating at the Client Side, but isnt very effecient!!
Thats why, i am looking for some method to concatenate text datatypes
i haven't had much luck so far. Hope something comes up soon
-
Maybe I can help you with concatenating on the client side...
read this for an exemple of a quick concatenation script :
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply