July 17, 2015 at 3:06 am
i Have a table with a column Capacity which is char(10) and gets populated from user files. I want to check records which have negative Capacity value. So i first checked if its numeric and then for negative.
select * from table WHERE ISNUMERIC(LTRIM(RTRIM(Capacity))) = 1 AND Capacity < 0
BUT still it checks for char fields too giving errors like - Conversion failed when converting the varchar value 'asdf ' to data type int.
Please help
July 17, 2015 at 3:24 am
nidhi.naina (7/17/2015)
i Have a table with a column Capacity which is char(10) and gets populated from user files. I want to check records which have negative Capacity value. So i first checked if its numeric and then for negative.select * from table WHERE ISNUMERIC(LTRIM(RTRIM(Capacity))) = 1 AND Capacity < 0
BUT still it checks for char fields too giving errors like - Conversion failed when converting the varchar value 'asdf ' to data type int.
Please help
Maybe something like this?
with ValidCapacities
as (select *
,nCapacity = try_parse( capacity as int)
from
where try_parse(capacity as int) is not null
)
select *
from ValidCapacities
where ValidCapacities.nCapacity < 0;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 17, 2015 at 3:28 am
I was trying to get a solution and came up with this -
select * from table WHERE ISNUMERIC(LTRIM(RTRIM(Capacity))) = 1 AND Capacity < '0'
It is working fine now without error. Do u think its the right way?
July 17, 2015 at 3:45 am
nidhi.naina (7/17/2015)
I was trying to get a solution and came up with this -select * from table WHERE ISNUMERIC(LTRIM(RTRIM(Capacity))) = 1 AND Capacity < '0'
It is working fine now without error. Do u think its the right way?
No. IsNumeric() is a horrible function! Have a look here and you'll understand why: http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/[/url]
And doing a character comparison is not going to be reliable for you. Try running this:
declare @capacity varchar(10) = '0999';
if @capacity < '1'
begin
select 'Capacity is less than 1';
end;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 17, 2015 at 4:20 am
Then how about Left(Capacity,1) = '-'
July 17, 2015 at 4:43 am
nidhi.naina (7/17/2015)
Then how about Left(Capacity,1) = '-'
Why are you ignoring my suggestion?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 17, 2015 at 4:57 am
Gosh... I am sorry i am not ignoring... i trying to find some simpler way if any else i have your suggestion in store. Looking for something easier as i have to check this for different tables and columns in a procedure.
Sorry if it was offending 🙂
July 17, 2015 at 5:00 am
Hi Nidhi,
Try this,
Declare @k char(10)
Declare @l as Char(10)
Set @k ='-88'
set @L = '88'
Select IIF( ISNUMERIC(@k) = 1,
Case
When Cast(@k as integer) > 0 then 'Positive'
When Cast(@k as integer) < 0 then 'Negative'
Else ''
End
,'IsnotNumeric') 'K Value'
,
IIF( ISNUMERIC(@L) = 1,
Case
When Cast(@L as integer) > 0 then 'Positive'
When Cast(@L as integer) < 0 then 'Negative'
Else ''
End
,'IsnotNumeric') 'L Value'
Output :
K ValueL Value
NegativePositive
Thanks,
Srikanth S
July 17, 2015 at 5:10 am
Srikanth vathsala (7/17/2015)
Hi Nidhi,Try this,
Declare @k char(10)
Declare @l as Char(10)
Set @k ='-88'
set @L = '88'
Select IIF( ISNUMERIC(@k) = 1,
Case
When Cast(@k as integer) > 0 then 'Positive'
When Cast(@k as integer) < 0 then 'Negative'
Else ''
End
,'IsnotNumeric') 'K Value'
,
IIF( ISNUMERIC(@L) = 1,
Case
When Cast(@L as integer) > 0 then 'Positive'
When Cast(@L as integer) < 0 then 'Negative'
Else ''
End
,'IsnotNumeric') 'L Value'
Output :
K ValueL Value
NegativePositive
Thanks,
Srikanth S
Please read the article I linked to on why IsNumeric() cannot be relied on.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 17, 2015 at 5:11 am
nidhi.naina (7/17/2015)
Gosh... I am sorry i am not ignoring... i trying to find some simpler way if any else i have your suggestion in store. Looking for something easier as i have to check this for different tables and columns in a procedure.Sorry if it was offending 🙂
Thanks for explaining. No offence taken, I was just wondering why you were persisting with other ideas.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 17, 2015 at 5:15 am
Simplified version.
select *
from
where try_parse(Capacity as int) < 0;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 17, 2015 at 6:16 am
Thanks.
So you think there can be some other options? As i have quite a few checks in where clauses for different tables and columns.
Or should i go with the first solution given by you.
July 17, 2015 at 6:22 am
nidhi.naina (7/17/2015)
Thanks.So you think there can be some other options? As i have quite a few checks in where clauses for different tables and columns.
Or should i go with the first solution given by you.
There are always other options. If you explain what is wrong with my simplified option, which is actually simpler that your initial query, I would appreciate it.
I gave you a simplified version and now you want to use the earlier (pre-coffee!) more-complex version instead, having previously complained about it? I don't understand what the problem is here.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 17, 2015 at 6:33 am
The reason for getting errors on queries like
select * from table WHERE ISNUMERIC(LTRIM(RTRIM(Capacity))) = 1 AND Capacity < 0
Is that SQL is a declarative language, so even if you write the code left to right, it doesn't mean that it will be executed in that order. SQL Server might decide to check the second condition first if it thinks it might be better to do it that way.
Using try_parse, try_convert, try_cast is a great advantage when using 2012+ versions and you should use them instead of unreliable or complex code.
July 17, 2015 at 3:19 pm
SELECT Capacity
FROM (
SELECT '1234' AS Capacity UNION ALL
SELECT '-987' UNION ALL
SELECT NULL UNION ALL
SELECT '123D4' UNION ALL
SELECT '1,234' UNION ALL
SELECT '+123' UNION ALL
SELECT '-12'
) AS test_data
WHERE
--must be a single negative sign followed by only numeric digits
Capacity LIKE '-%' AND
SUBSTRING(Capacity, 2, 100) NOT LIKE '%[^0-9]%'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply