Sql-server – Numeric comparisons on string column data


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

Best Solution

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

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.