Prev: sample code for code for "sql server development with access 2000" by Wrox
Next: Can Someone please help me with my Computer stuff? 21692
From: steve on 26 Jan 2010 17:22
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

From: Erland Sommarskog on 26 Jan 2010 17:49
steve (tinker123(a)gmail.com) writes:
> 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?

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(a)sommarskog.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/prodinfo/previousversions/books.mspx

From: steve on 27 Jan 2010 11:48
On Jan 26, 5:49 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote:
> steve (tinker...(a)gmail.com) writes:
> > 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?
>
> 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?

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
From: Erland Sommarskog on 27 Jan 2010 17:49
steve (tinker123(a)gmail.com) writes:
> 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)

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(a)sommarskog.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/prodinfo/previousversions/books.mspx

From: Iain Sharp on 28 Jan 2010 04:11
On Wed, 27 Jan 2010 22:49:06 +0000 (UTC), Erland Sommarskog
<esquel(a)sommarskog.se> wrote:

>steve (tinker123(a)gmail.com) writes:
>> 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)
>
>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.

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+'%'
 | 
Pages: 1
Prev: sample code for code for "sql server development with access 2000" by Wrox
Next: Can Someone please help me with my Computer stuff? 21692