Im getting this when running a query like this WITHOUT inserting in any table. in SSMS 2008.
select iav.*
from ITEM_ATTRIBUTE_VALUE iav
where iav.attribute_value != ''
and ISNUMERIC(iav.attribute_value) = 0
Why would it do this ?
Add a comment
|
4 Answers
For a start you will probably want "<>" as opposed to "!=" in SQL Server.
Also, ISNUMERIC will probably have to truncate iav.attribute_value to try and test if it is a number, probably to the length of maximum int which is 2147483647 (signed) - so to 10 characters long - hence the warning.
Edit:
If you look at the spec of ISNUMERIC you can see it looks for anything up to BIGINT.
BIGINT's maximum is 9223372036854775807, which is 19 characters long. So if your attribute_value is greater than 19 characters long it will be truncated in an attempt to test it is a valid number that is usable within SQL Server.
5 Comments
Storm
ok, I tried the same query with ISNUMERIC(LEFT(iav.attribute_value, 5)) = 1 /* not 0 this time */ and iav.item_id = 74261; and it returns 1 record and that is item_id | attribute_value ------- ---------------- 74261 ٠٠٠٠ ?? whats happening
Storm
ok that doesnt look as formatted as i typed it in. results were item_id = 74261 attribute_value = ٠٠٠٠
joshcomley
Well it seems clear what is happening: it's found that a row with an item_id of 74261, checked the first five characters of attribute_value are numeric and returned the row! Without knowing more about your setup it's difficult to determine otherwise. Is item_id a primary key? Or does it have a UNIQUE constraint? If so, then of course you'll only get one result!
Storm
no, theres only 1 row that matches item_id 74261 and the full value in that row is ".....", thats the problem !
joshcomley
Do you mean the full value is actually just periods - i.e. "....."? The dots don't represent something?
Is it possible that the length of all fields returned, is greater then 8000 characters?
I think that might cause that error.
Try only returning the columns you need, or just a portion of a column, if it's greater then 8000 characters in length.
Comments
The main reason is one must be taking a temporary table(or something same) within the stored procedure and temporary table's datatype and the actual table's datatype must be having a mismatch in size.
Comments
ISNUMERIC has a character limit of 308 integer chars and in some cases 309 chars for which it will return true if that is the case, after a certain amount of characters it will give the above mentioned error.
Best way to do the ISNUMERIC Check is to split your string into 300 char sections, validate each section and return the answer. This can be achieved via Function or just straight through code
DECLARE @incomingString VARCHAR(MAX) = '133450276206256972456897349683... Until 999999'
DECLARE @parts INT = 1
DECLARE @Count INT = 0
DECLARE @ISNUMERIC BIT = 1
WHILE(@parts * 300 < LEN(@incomingString))
SET @parts = @parts + 1
WHILE(@Count < @parts)
BEGIN
SET @Count = @Count + 1
IF(ISNUMERIC(SUBSTRING(@incomingString, (@Count * 300) - 299, 300)) = 0)
SET @ISNUMERIC = 0
END
SELECT CASE WHEN @ISNUMERIC = 0 THEN 'FAIL' ELSE 'SUCCESS' END
