I have a table with a text column
I want to select that column and add some text to the result
e.g.
select textcolumn + 'Added Text' From xTable
but this fails because one can not use + or concat on text columns
i get around this by using cast as varchar(1024)
e.g.
select cast(textcolumn as varchar(1024)) + 'Added Text' From xTable
but this would cut the text column after 1024 chars
is there a way around this and not limiting the length of the text columnI'm at home and not a work at the moment so I can't check for sure (or rather I'm not going to), but can't you omit the length of the varchar in the cast statement??|||select cast(textcolumn as varchar) + 'Added Text' From xTable
or
select convert(varchar, textcolumn) + 'Added Text' From xTable
works for my SQL2000, otherwise, you can just trim out the trailing spaces|||Just be aware that the max length of varchar is 8000.
Originally posted by shianmiin
select cast(textcolumn as varchar) + 'Added Text' From xTable
or
select convert(varchar, textcolumn) + 'Added Text' From xTable
works for my SQL2000, otherwise, you can just trim out the trailing spaces
Showing posts with label textcolumn. Show all posts
Showing posts with label textcolumn. Show all posts
Tuesday, February 14, 2012
Sunday, February 12, 2012
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
>
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
>
Labels:
2000i,
additional,
appreciate,
concatenate,
concatenation,
database,
grief,
microsoft,
mysql,
oracle,
server,
sql,
text,
textcolumn
Subscribe to:
Posts (Atom)