Hi;

I'm trying to find the number of records in a table where field_1 is
not a substring in field_2.

My problem with the query below is that field_2 is a text field and is
too fat to convert in order to use the LIKE operator ( I get "field
would be truncated" messages )


select count(*) as COUNT_Bad_Records from MyTable
where
field_1 not like CAST(field_2 as varchar(max)) +'%'

Any suggestions for how else I might get to the same result?

Thanks much in advance

steve (tinker123 (AT) gmail (DOT) com) writes:
I suspect that there is something you are not telling us. I tried
this on SQL 2005:

CREATE TABLE sometable (field_1 text NULL, field_2 text NULL)

select count(*) as COUNT_Bad_Records from sometable
where
field_1 not like CAST(field_2 as varchar(max)) +'%'
go
drop table sometable

and it complete without error.

Could you post the actual code and the actual error message?


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Jan 26, 5:49*pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
That is the actual query except for changing the name of the fields.

This is the error message I got:

-----------------------------------------------------------------------------------
Server: Msg 8152, Level 16, State 10, Line 1
String or binary data would be truncated.
------------------------------------------------------------------------------------

both field_1 and field_2 are the text datatype.

I used the query successfully on other tables with the same structure
so I am guessing field_2 in this table just has more data than will
fit into a varchar(max)

Thanks

Steve

steve (tinker123 (AT) gmail (DOT) com) writes:
This message occurs with an UPDATE, INSERT or MERGE statement, when
you try to put more data into a string or binary column for which
there are space. To my knowning, you cannot get this error with the SQL
statement you posted.

varchar(MAX) can fit just as much data as the text data type.

Just for fun, run rhis query:

SELECT max(datalength(field_1)), max(datalength(field_2))
FROM sometable

I think you should examing the context where you get this error. Most
likely the error is caused by some other statement.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Wed, 27 Jan 2010 22:49:06 +0000 (UTC), Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

Unless you got your description wrong, you also have the test
backwards.

To test if field_1 is a substring of field_2

select field_1
from table where not field_2 like N'%'+field_1+'%'