Comparing text in WHERE

Hi everyone,

I am compare to text fields like this:

CREATE PROCEDURE dbo.PCAttByVal
(
@.Value text
)
AS
BEGIN

SET NOCOUNT ON
SELECT ID FROM PCAtt WHERE Value=@.Value

END

But the environment (I use VS2005) says that the data type is incompatible with the equal operator. Then I tried:

CREATE PROCEDURE dbo.PCAttByVal
(
@.Value text
)
AS
BEGIN

SET NOCOUNT ON
SELECT ID FROM PCAtt WHERE Value IN '('+@.Value+')'

END

But nada (nothing). Any ideas? I can't change the data type since this field will hold values of different sizes.

Thank you for your input in advance!

hi,

text/ntext datatype does not support this kind of operation/comparison... you can find the "available" methods against these datatypes in http://msdn2.microsoft.com/en-us/library/ms187993.aspx..

these datatype are not good candidates for "filtering" operations as well.. consider that text can hold up to 2gb of data, and it's not worth the problem to support such heavy features..

so you can end up with "workarounds" if they fit your needs, similar to

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( Id int NOT NULL PRIMARY KEY, Value text NULL ); GO INSERT INTO dbo.TestTB VALUES ( 1 , 'some text' ); INSERT INTO dbo.TestTB VALUES ( 2 , 'some other text' ); GO DECLARE @.key varchar(10); SET @.key = 'some text'; SELECT * FROM dbo.TestTB WHERE SUBSTRING( Value, 1, DATALENGTH(@.key)) = @.key; GO DROP TABLE dbo.TestTB; --<- Id Value -- 1 some text

but consider the other methods as well..

if you are using SQL Server 2005, consider moving the text/ntext datatypes to varchar(MAX)/nvarchar(MAX), as text datatype has been deprecated and, more usefull, varchar(MAX) supports all the traditional "string" operations...

regards

sqlsql

comma separated resultant column

I would like the resultant column to be comma separated. Here is sample ddl
set nocount on
go
create table z_my_tbl_del
( i int,
n char(8)
)
go
insert z_my_tbl_del values(1,'john')
insert z_my_tbl_del values(2,'mary')
insert z_my_tbl_del values(3,'luke')
insert z_my_tbl_del values(4,'trisha')
insert z_my_tbl_del values(5,'sam')
go
select * from z_my_tbl_del
go
drop table z_my_tbl_del
go
The resultant column should be all in one line as as opposed to separate
lines.
n
======
john,mary,luke,trisha,samThe right way is to do display formatting in the front end. But if you
like doing things wrong and destroying 1NF, use a cursor. Someone else
will show you a proprietary way of doing .|||See if this helps:
http://groups-beta.google.com/group...
5bf366dd9e73e
AMB
"sqlster" wrote:

> I would like the resultant column to be comma separated. Here is sample dd
l
> set nocount on
> go
> create table z_my_tbl_del
> ( i int,
> n char(8)
> )
> go
> insert z_my_tbl_del values(1,'john')
> insert z_my_tbl_del values(2,'mary')
> insert z_my_tbl_del values(3,'luke')
> insert z_my_tbl_del values(4,'trisha')
> insert z_my_tbl_del values(5,'sam')
> go
> select * from z_my_tbl_del
> go
> drop table z_my_tbl_del
> go
> The resultant column should be all in one line as as opposed to separate
> lines.
> n
> ======
> john,mary,luke,trisha,sam|||Somehow I get the feeling that comma-separated values are really *in* this
year.
These things do not belog on the data layer!
But, that's fashion - useless but neat...
Look at this:
http://milambda.blogspot.com/2005/0...s-as-array.html
And what's bad about it? One of the things is the mangled informational
value - the actual relationship between the values 'john', 'mary', 'luke',
'trisha' and 'sam' gets distorted. Are these values cummulative or
alternative? What do they have in common? Did they have anything in common
before? Can they have anything in common ever again?
Anyway, use with care...
ML|||i suggest the following :
SELECT TOP 1 z_my_tbl_del_1.n + ',' + z_my_tbl_del_2.n + ',' +
z_my_tbl_del_3.n + ',' + z_my_tbl_del_4.n + ',' + z_my_tbl_del_5.n AS nn
FROM z_my_tbl_del z_my_tbl_del_1 INNER JOIN
z_my_tbl_del z_my_tbl_del_2 ON z_my_tbl_del_1.i <>
z_my_tbl_del_2.i INNER JOIN
z_my_tbl_del z_my_tbl_del_3 ON z_my_tbl_del_2.i <>
z_my_tbl_del_3.i AND z_my_tbl_del_1.i <> z_my_tbl_del_3.i INNER JOIN
z_my_tbl_del z_my_tbl_del_4 ON z_my_tbl_del_3.i <>
z_my_tbl_del_4.i AND z_my_tbl_del_1.i <> z_my_tbl_del_4.i AND
z_my_tbl_del_2.i <> z_my_tbl_del_4.i INNER JOIN
z_my_tbl_del z_my_tbl_del_5 ON z_my_tbl_del_1.i <>
z_my_tbl_del_5.i AND z_my_tbl_del_2.i <> z_my_tbl_del_5.i AND
z_my_tbl_del_3.i <> z_my_tbl_del_5.i AND
z_my_tbl_del_4.i <> z_my_tbl_del_5.i
Try it!!!
mario
"sqlster" wrote:

> I would like the resultant column to be comma separated. Here is sample dd
l
> set nocount on
> go
> create table z_my_tbl_del
> ( i int,
> n char(8)
> )
> go
> insert z_my_tbl_del values(1,'john')
> insert z_my_tbl_del values(2,'mary')
> insert z_my_tbl_del values(3,'luke')
> insert z_my_tbl_del values(4,'trisha')
> insert z_my_tbl_del values(5,'sam')
> go
> select * from z_my_tbl_del
> go
> drop table z_my_tbl_del
> go
> The resultant column should be all in one line as as opposed to separate
> lines.
> n
> ======
> john,mary,luke,trisha,sam