Concatenate multiple text fields in a view

Question

Post reply

Concatenate multiple text fields in a view

Alexander de Rooij

SSC Enthusiast

Points: 119
More actions
May 11, 2010 at 2:37 am

#218789

Hi all,
My problem is as follows:
I have a table that contains fields as shown below
ctxt int
clan char(1)
seqe int
text binary(240)
the ctxt column contains a textnumber which identifies 1 complete text in the application
When there are too many lines for a text a second seqe field (record) is created and the text is continued in the second record etc.
The clan field contains a string that holds the language code.
I need to create a view that contains all the text for 1 ctxt number per language.
Has someone done this before ????
Any help would be appriciated
Greetings Alexander

Viewing 5 posts - 1 through 4 (of 4 total)

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

matzer SSCrazy Eights Points: 9727 More actions · Answer 1

Here's an example of string concatenation. Be sure to cast the text field to varchar(max) otherwise it won't work.

create table #ConcatTest(id int, v text)

insert into #ConcatTest

select 1, 'Aa' union all

select 1, 'B<' union all

select 1, 'C''''' union all

select 2, 'A"' union all

select 2, 'A>' union all

select 3, 'A&' union all

select 3, 'Ac' union all

select 3, 'Ad' union all

select 3, 'Ae'

select * from #ConcatTest

select

c1.id,

stuff((select

', ' + cast(c2.v as varchar(max))

from

#ConcatTest c2

where

c2.id = c1.id

for xml path(''),type).value('.', 'varchar(max)'), 1, 1, '')

--for xml path('')), 1, 1, '') -- The wrong way with xml escape codes

from

#ConcatTest c1

group by

c1.id

drop table #ConcatTest

Alexander de Rooij SSC Enthusiast Points: 119 More actions · Answer 2

Hi Peter,

This does exactly what I wanted, only thing is that my string is getting to long and all the text does not fit into 1 field.

Thanks a lot

matzer SSCrazy Eights Points: 9727 More actions · Answer 3

Alexander de Rooij (5/11/2010)
Hi Peter,
This does exactly what I wanted, only thing is that my string is getting to long and all the text does not fit into 1 field.
Thanks a lot

Does this mean your string will become larger then 2Gb (max size of varchar(max))?

Peter

Alexander de Rooij SSC Enthusiast Points: 119 More actions · Answer 4

Hi Peter,

It's been a while but I have 1 other question, I tried to do a select on my table with the text sequences concatenated in 1 field but when I run the Query I get the message below

Msg 6841, Level 16, State 1, Line 1

FOR XML could not serialize the data for node 'NoName' because it contains a character (0x001D) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.

Any solution for this, I tried the convert to binary or varbinary already but than the texts are unreadable

Thanks in advance

Alexander de Rooij