Wrapping TEXT field in quotes

[jasonsalas]

hi all,

i'm trying to export a DB table i've got to CSV, part of which requires me to have to wrap a field of type TEXT in quotations ("). concatenation doesn't work because SQL Server thinks i'm doing an addition operation:

SELECT id,title,'"'+body+'"' as [body] FROM table

i tried CAST'ing the field to VARCHAR, but that won't work because it only support a max of 8,000 characters, so i lose a lot of data.

anyone got any other ideas how i can surround the field with quotations? thanks so much!

 

[bborissov]

What version of SQL Server you use?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[jasonsalas]

i'm running SQL Server 2000. thanks!

 

[simian101]

Try this...

SELECT id, title,
char(34) + 'body' + char(34) as [body]
FROM table

Simi

 

[jasonsalas]

hi simi,

that didn't take either. the issue is the '+' concatenation operator...TEXT fields see it as trying to do addition, which causes it to error.

 

[gmmastros]

The problem is that SQL2000 text columns are extremely difficult to work with. In SQL2005, you could change the data type to varchar(max) and then all of the normal string handling functions work properly.

If you were looking for a reason to upgrade.... this is it.

Otherwise, you're probably stuck dealing with TextPtr and updatetext.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jasonsalas]

thanks george! yeah, i'm learning this is quite the challenge!

 

[RyanEK]

Hi,

You could create a temporary table with the varchar(max) field defined that contains a copy of the primary key and text field. You could then join to that.

Ryan

 

[jasonsalas]

hi ryan,

i've found that i can't do VARCHAR(MAX) because i'm running SQL 2000. my host is nice enough to migrate just that table to SQL 2005.

 

[RyanEK]

eg.

declare @yourtable table (field text)
insert into @yourtable values('text data')

declare @test2 table (field2 varchar(max))
insert into @test2 select * from @yourtable

select '"' + field2 + '"' from @test2

Ryan