SQL Assistance

[Garf]

Hi

can someone help me with the following

I have two fields in a table that need to be concatenated into a third field in the following format

Field1, ' ','-',' ', Field2

Here's the fun bit. all three fields are ntext.

Oh this is MS SQL2000 not 2005 so dont even get me started on varchar(max)

I know its something to do with updatetext but I've nevr used it.

[Lazlo]

Can't you just do a normal INSERT using the + operator to concatenate?

[Lazlo]

Sorry, UPDATE obviously, not INSERT (duh)

[Garf]

Originally Posted by Lazlo

Sorry, UPDATE obviously, not INSERT (duh)

NO, UPDATE DOESN'T WORK FOR NTEXT FIELDS


whoops, sorry

edit: however, moving the table temporarily to SQL2005 and then doing this works...

UPDATE MIGRATION_COMMUNICATION SET COMM_NOTE=RTRIM(COALESCE((CAST(DOCDESC AS VARCHAR(MAX))),'')) + ' '+'-'+' ' +
RTRIM(COALESCE((CAST(MEMO AS VARCHAR(MAX))),'')) FROM MIGRATION_COMMUNICATION

[Lazlo]

Try WRITETEXT instead of UPDATE, something like this. I'm guessing on the exact syntax here cos I don't have a SQL2000 database to play with.

WRITETEXT MIGRATION_COMMUNICATION.COMM_NOTE
(SELECT RTRIM(DOCDESC) + ' '+'-'+' ' + RTRIM(MEMO) FROM MIGRATION_COMMUNICATION)

[wilbo]

Originally Posted by Garf

edit: however, moving the table temporarily to SQL2005 and then doing this works...

Pfft. stroke.

[Garf]

Originally Posted by wilbo

Pfft. stroke.

actually my solution is a bit strokey tbh. Using SQL2005 kit to make up for a lack of sql 2000 knowledge.

[Lazlo]

Update to the above. Looks like you need a TEXTPTR as part of the syntax. So the above gets changed to:

DECLARE @ptrval varbinary(16)

SELECT @ptrval = TEXTPTR(MIGRATION_COMMUNICATION.COMM_NOTE)

WRITETEXT MIGRATION_COMMUNICATION.COMM_NOTE @ptrval
(SELECT RTRIM(DOCDESC) + ' '+'-'+' ' + RTRIM(MEMO) FROM MIGRATION_COMMUNICATION)