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

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.

Code:

---

UPDATE notificationMessageTbl SET userReadID = '12,'

---

Thanks in advance

Simon

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

Code:

---

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).

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

Which database system are you using?

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

Code:

---

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.

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

Code:

---

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

---

Also I am using SQLServer

Thanks

Simon

That's correct. :)

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

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

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

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

Line 1: Incorrect syntax near 'Len'.

Thanks

Simon

Try this

Code:

---

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

---

16 is len of TEXT datatype