i need a help n query
"totalexp" is a nvarchar field ... in a table and i need to select like below
select EmpId,FirstName,totalexp from sample where totalexp > '6'
empid firstname totalexp
1 Me 8.2
5 as 6
10 567 64 mon
11 leader 6+ yrs
12 admintest 6.3
16 G 6
i have values like 11, 12, 21 and all its not displaying those things
If you can't change your column type, you have to use CAST or CONVERT before you do the comparison. Your code would be:
SELECT EmpId, FirstName, TotalExp
FROM sample
WHERE CAST(TotalExp AS INT) > 6
A bit of warning: with your current DB structure, anyone can insert TotalExp as 'One' or 'Two' or any other arbitrary string, and your query will fail. Not really something you want to happen, seriously.
AS DECIMAL (maybe this helps someone)I know the question is very old. Just posting the answer because it might be helpful for some one.
The below code can be used in scenarios where there exists non numeric values for other rows.
SELECT EmpId,FirstName,totalexp
FROM sample
WHERE
(CASE
WHEN (ISNUMERIC(totalexp) = 1)
THEN CAST(LTRIM(RTRIM(totalexp)) AS float)
ELSE 0 END) > 6
The code is tested working
The mess starts with TotalExp being nvarchar, as it contains data which may be "6 months", "6 years", "6+yrs" etc. What is the intention behind
where totalexp > '6'
? 6 years, 6 months, 65 days?
You need to convert the data into a numeric format, for example a number of months that you can compare against some requirement (as 'months experience').
However, in one year your data will become obsolete as it does not change, as you would except that every TotalExp which now states "6 yrs" should then be "7 years" (if that skill has been practiced in the meantime).
So for active skills it would be preferable to have an ExperienceSince DATETIME field, which has the nice effect that its resulting "total experience" is always up-to-date.
My primary recommendation to you would be to perform a data cleaning exercise in order to standardise the column data. At present there are multiple forms/standards of data within a single column.
Should you wish to ignore character data completely however, then in order to account for column values that will not convert naturally to an integer you could make use of the isnumeric() function to test for column data as being numeric.
See the following example for details:
create table #testTable
(
NumericAsString nvarchar(100)
);
insert into #testTable (NumericAsString) values('1');
insert into #testTable (NumericAsString) values('4');
insert into #testTable (NumericAsString) values('28');
insert into #testTable (NumericAsString) values('32');
insert into #testTable (NumericAsString) values('11232');
insert into #testTable (NumericAsString) values('fdsfdfas');
insert into #testTable (NumericAsString) values('wtwtrwtw');
select *
from #testTable;
select NumericAsString
from #testTable
where
(
case
when isnumeric(NumericAsString) = 1 then convert(int, NumericAsString)
else 0
end)
> 6
drop table #testTable;
Perhaps 'totalexp' needs to be a number field (int etc) rather than nvarchar for the comparison in the where clause to work. Using nvarchar to store numbers is not a good idea.
Try using Cast or Convert on you where clause to change the type of totalexp to an integer.
SELECT EmpId,FirstName,totalexp
FROM sample
WHERE CONVERT(int ,totalexp) > 6
