Concatenation

SQL Server 2000
I'm having a lot of grief trying to concatenate additional text to a text
column and would appreciate some help.
Here's some sample statements:
CREATE TABLE Test (s TEXT)
INSERT INTO TEST(s) VALUES ('The cat')
UPDATE TEST SET s = s + ' on the mat'
The last line above results in:
Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals add, type equals text.
I've tried everything I can think of. Casting or converting the literal to
TEXT returns the same error and:
UPDATE TEST SET s = {fn CONCAT(s, ' on the mat')}
results in:
Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals concatenation, type equals
text.
Help!
TIA,
Geofflook at readtext and writetext and the update statement for sql server 05
with .WRITE
"Geoff Lane" <geoff@.nospam.gjctech.co.uk> wrote in message
news:Xns97DBBE37BA208gjctcswxnsrt@.207.46.248.16...
> SQL Server 2000
> I'm having a lot of grief trying to concatenate additional text to a text
> column and would appreciate some help.
> Here's some sample statements:
> CREATE TABLE Test (s TEXT)
> INSERT INTO TEST(s) VALUES ('The cat')
> UPDATE TEST SET s = s + ' on the mat'
> The last line above results in:
> Server: Msg 403, Level 16, State 1, Line 1
> Invalid operator for data type. Operator equals add, type equals text.
> I've tried everything I can think of. Casting or converting the literal to
> TEXT returns the same error and:
> UPDATE TEST SET s = {fn CONCAT(s, ' on the mat')}
> results in:
> Server: Msg 403, Level 16, State 1, Line 1
> Invalid operator for data type. Operator equals concatenation, type equals
> text.
> Help!
> TIA,
> --
> Geoff|||Try this instead:
CREATE TABLE Test (s text)
INSERT INTO TEST(s) VALUES ('The cat')
UPDATE TEST
SET s = cast(s as varchar(50)) + ' on the mat'
You can insert a varchar into a text but cannot use the + for a text
field.
Geoff Lane wrote:
> SQL Server 2000
> I'm having a lot of grief trying to concatenate additional text to a text
> column and would appreciate some help.
> Here's some sample statements:
> CREATE TABLE Test (s TEXT)
> INSERT INTO TEST(s) VALUES ('The cat')
> UPDATE TEST SET s = s + ' on the mat'
> The last line above results in:
> Server: Msg 403, Level 16, State 1, Line 1
> Invalid operator for data type. Operator equals add, type equals text.
> I've tried everything I can think of. Casting or converting the literal to
> TEXT returns the same error and:
> UPDATE TEST SET s = {fn CONCAT(s, ' on the mat')}
> results in:
> Server: Msg 403, Level 16, State 1, Line 1
> Invalid operator for data type. Operator equals concatenation, type equals
> text.
> Help!
> TIA,
> --
> Geoff|||"Gary Gibbs" <ggibbs@.aahs.org> wrote in news:1149702816.828060.156320
@.i40g2000cwc.googlegroups.com:

> Try this instead:
> CREATE TABLE Test (s text)
> INSERT INTO TEST(s) VALUES ('The cat')
> UPDATE TEST
> SET s = cast(s as varchar(50)) + ' on the mat'
>
> You can insert a varchar into a text but cannot use the + for a text
> field.
Thanks a million.
FWIW, I've just tried:
CAST(s AS VARCHAR) + ' on the mat'
and it worked. This is good! s is a text column because it's a "comment"
field that I expect to occasionally grow to over a couple of thousand
characters.
Thanks again,
Geoff|||There is another (and probably better) way to accomplish this. Check
BOL about UPDATETEXT.
Gary Gibbs wrote:
> Try this instead:
> CREATE TABLE Test (s text)
> INSERT INTO TEST(s) VALUES ('The cat')
> UPDATE TEST
> SET s = cast(s as varchar(50)) + ' on the mat'
>
> You can insert a varchar into a text but cannot use the + for a text
> field.
> Geoff Lane wrote:|||> FWIW, I've just tried:
> CAST(s AS VARCHAR) + ' on the mat'
> and it worked. This is good! s is a text column because it's a "comment"
> field that I expect to occasionally grow to over a couple of thousand
> characters.
Some comments.
(1) always specify a length for VARCHAR(). CAST(s AS VARCHAR(2048)) for
example is much more reliable and predictable than CAST(s AS VARCHAR). In
some circumstances you will get a VARCHAR(1) and in others you will get a
VARCHAR(30). If you run that update statement against a column that has
more than 30 characters, I think you will see truncation (but haven't tested
it). Just always specify a length.
(2) if you are only expecting a couple thousand characters, then I suggest
using VARCHAR(4000) or VARCHAR(8000). N/VARCHAR(MAX) in SQL 2005 is much
easier to work with, and doesn't have quite as many limitations as
TEXT/NTEXT, but should still be reserved for required usage.
A|||Beware: casting as a VARCHAR without specifying a size defaults to 30
characters. If your s column contains values larger than 30 characters,
they will be truncated. I.e.:
DECLARE @.s CHAR(4000)
SELECT @.s = 'ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLM
NOPQRSTUVWXYZ'
SELECT @.s, LEN(@.s) -- 52 characters
SELECT CAST(@.s AS VARCHAR), LEN(CAST(@.s AS VARCHAR)) -- truncates to 30
characters
"Geoff Lane" <geoff@.nospam.gjctech.co.uk> wrote in message
news:Xns97DBC1F645AB4gjctcswxnsrt@.207.46.248.16...
> "Gary Gibbs" <ggibbs@.aahs.org> wrote in news:1149702816.828060.156320
> @.i40g2000cwc.googlegroups.com:
>
> Thanks a million.
> FWIW, I've just tried:
> CAST(s AS VARCHAR) + ' on the mat'
> and it worked. This is good! s is a text column because it's a "comment"
> field that I expect to occasionally grow to over a couple of thousand
> characters.
> Thanks again,
> --
> Geoff|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
news:ePFgZ4liGHA.3848@.TK2MSFTNGP04.phx.gbl:

> Some comments.
> (1) always specify a length for VARCHAR(). CAST(s AS VARCHAR(2048))
> for example is much more reliable and predictable than CAST(s AS
> VARCHAR). In some circumstances you will get a VARCHAR(1) and in
> others you will get a VARCHAR(30). If you run that update statement
> against a column that has more than 30 characters, I think you will
> see truncation (but haven't tested it). Just always specify a length.
Yep - you're correct and I posted too soon. Shortly after posting I
discovered that my text column was being truncated before the
concatentation and I've ended up using VBScript in the calling ASP to
kludge my way around it viz:
strQuery = "SELECT s FROM myTable WHERE tID=" & tID
rs.Open strQuery, conn
strS = rs("s")
rs.Close
strQuery = "UPDATE myTable " & _
"SET s = '" & strS & " on the mat' " & _
"WERE tID=" & tID
conn.Execute strQuery
Yes, I know that it's messy and hits the database too many times - but it
works. The application is fairly lightly loaded so the performance hit
isn't too drastic. That said, I intend investigating Gary's suggestions
tomorrow - although I am limited to SQL Server 2000 until at least the
end of this year and so can't use anything introduced with 2005!

> (2) if you are only expecting a couple thousand characters, then I
> suggest using VARCHAR(4000) or VARCHAR(8000). N/VARCHAR(MAX) in SQL
> 2005 is much easier to work with, and doesn't have quite as many
> limitations as TEXT/NTEXT, but should still be reserved for required
> usage.
I'm expecting the field length to regularly exceed 2000 characters but a
user might want to write almost their life story there. For that reason,
I don't want to limit the field length (although they'd need to go some
to break VARCHAR(8000) !) Perhaps the introduction of VARCHAR(MAX) is the
catalyst for conversion to 2005 :)
Thanks to all,
Geoff|||> Beware: casting as a VARCHAR without specifying a size defaults to 30
> characters.
And in other cases, 1 character, for example DECLARE.
DECLARE @.foo VARCHAR;
SET @.foo = 'abc';
SELECT @.foo;
A|||True. I was talking just about CASTing a value, not the DECLARE statement,
since he's casting from a TEXT column.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uxFcqFmiGHA.2220@.TK2MSFTNGP05.phx.gbl...
> And in other cases, 1 character, for example DECLARE.
> DECLARE @.foo VARCHAR;
> SET @.foo = 'abc';
> SELECT @.foo;
> A
>