How to append text to memo field?

Question

I'm using Advantage Database Server 9.10, and I want to append a text to Memo field.

I tried this way,

UPDATE 
  myTable 
SET 
  memo =   cast(memo as SQL_VARCHAR(max)) 
         + cast(' Appended string' as SQL_VARCHAR(max)) 
WHERE 
  pk = '80'

but it does not work,

anybody know how to do this?

Does not work = what. Invalid sql no change to data, memo doesn't end up with what you expected? — Tony Hopkinson, Jun 05 '12 at 13:37
right that is invalid sql. and I want to this, supposing memo field(memo type) has 'ABC' value and I want to append to 'DEF' after 'ABC', how should I write query for this? — Expert wanna be, Jun 05 '12 at 13:53
Don't know advantage myself, but some other boyz have weighed in. Can't say I like the idea. If I was using Text/Image/Blob, I'd be trying my hardest to only manipulate it client side. Encoding would cause some palpitations with this approach. — Tony Hopkinson, Jun 05 '12 at 18:50

score 2 · Answer 1 · answered Jun 05 '12 at 18:22

I think you can write it more simply as:

UPDATE 
  myTable 
SET 
  memo = memo + ' Appended string' 
WHERE 
  pk = '80'

If the memo field might be NULL and you don't want the result to be NULL, you can do something like this:

...
SET
  memo = ifnull( memo, '' ) + ' Appended string'
...

score 1 · Accepted Answer · answered Jun 05 '12 at 15:52

There is no VARCHAR(MAX) data type in ADS, but you can cast to SQL_VARCHAR or SQL_CHAR instead.

Correct syntax:

UPDATE
  myTable 
SET 
   memo =   cast(memo as SQL_VARCHAR) 
          + cast(' Appended string' as SQL_VARCHAR) 
WHERE 
   pk = '80'

You can find the valid list of data types for CAST and CONVERT in the ads documentation:

score 0 · Answer 3 · answered Jan 03 '14 at 03:51

This works for me, you just have to make sure you allow for enough characters in the VARCHAR():

UPDATE 
   myTable 
SET 
   memo = CONVERT(VARCHAR(8000),memo) + ' Appended String'
FROM 
   myTable
WHERE 
   pk = '80'

I added the FROM myTable because in my project I had to LEFT JOIN a couple of tables also.

3 Answers3