Problem added text to the end of existing text field...

[lidds]

Hi,

I need some help, as always, I currently have a table called 'notificationMessageTbl' and I have a TEXT field called 'userReadID' what I want to do is add text to the end of current text within the field using SQL

If I use the following code it just replaces the exsisting text with '12,' and does not append.

UPDATE notificationMessageTbl SET userReadID = '12,'

Thanks in advance

Simon

[si_the_geek]

You can use the field name to retrieve the existing value, and append it to your value, eg:

UPDATE notificationMessageTbl SET userReadID = userReadID + '12,'

I would recommend using a Where clause, as this currently alters all rows of your table (and is likely to have errors if the field is currently Null in any rows).

[lidds]

Unfortunately this give the following error which I think is because the field is a TEXT field not a varchar field....

Invalid operator for data type. Operator equals add, type equals text.

Thanks for the recommendation about the WHERE clause.

Thanks

Simon

[si_the_geek]

Which database system are you using?

Assuming SQL Server (I think that's the message it shows!), you could try this:

userReadID = Cast(userReadID as VarChar(Length(userReadID)))+ '12,'

..note that this will only work if the length of the field is within the limits of the VarChar data type.

[lidds]

Sorry to ask a stupid question, but will the code look like this:

UPDATE notificationMessageTbl SET userReadID = Cast(userReadID as VarChar(Length(userReadID)))+ '12,'

Also I am using SQLServer

Thanks

Simon

[si_the_geek]

That's correct.

[lidds]

Thanks, just wanted to check as I tried that and it gave the following error:

Line 1: Incorrect syntax near 'Length'.

Thanks for all your help

Simon

[si_the_geek]

Oops.. I sometimes get confused and use the syntax from one DBMS in another.

I just checked Books Online, and it looks like it should be Len instead of Length.

[lidds]

Sorry to be a pain , but it's still give me an error:

Line 1: Incorrect syntax near 'Len'.

Thanks

Simon

[amrita]

Try this

UPDATE notificationMessageTbl SET userReadID = Cast(userReadID as VarChar(16))+ '12,'

16 is len of TEXT datatype