Numeric comparisons on string column data

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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;

Answer 4

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

Answer 5

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.

Answer 6

Thank You Bhidu Solve my big problem.