CODEWITHSUNDEEP
mysqlsql
Rajib Sarker
Rajib Sarker

Reputation: 91

MySQL where condition on alpha-numeric column

My employee table has a column for work level containing values like 1A, 1B, 1C, 2A, 2B, & 2C. Employee hierarchy is 2C > 2B > 2A > 1C > 1B > 1A.

Now if I write a query like this: 

SELECT *
FROM employee
WHERE work_level >= 1C

Would it return the rows containing only work levels 1C, 2A, 2B, 2C?

If not then how can I?

Upvotes: 0

Views: 388

Answers (2)

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

Try something like this :

Let us say you have some work_levels and input as defined below : 

set @work_level1 = '25C';  
set @input_work_level1 = '1C';  

set @work_level2 = '2C';  
set @input_work_level2 = '15C';  

set @work_level3 = '2A';  
set @input_work_level3 = '1C';  

set @work_level4 = '2C';  
set @input_work_level4 = '15B';

Running the following query : 

select   
 ( cast( left(@work_level1, length(@work_level1)-1) as unsigned )
   >=
   cast( left(@input_work_level1, length(@input_work_level1)-1) as unsigned )
 )
 and
 right(@work_level1, 1) <= right(@input_work_level1, 1)
 as '''25C'' >= ''1C''',

 ( cast( left(@work_level2, length(@work_level2)-1) as unsigned )
   >=
   cast( left(@input_work_level2, length(@input_work_level2)-1) as unsigned )
 )
 and
 right(@work_level2, 1) <= right(@input_work_level2, 1)
 as '''2C'' >= ''15C''',

 ( cast( left(@work_level3, length(@work_level3)-1) as unsigned )
   >=
   cast( left(@input_work_level3, length(@input_work_level3)-1) as unsigned )
 )
 and
 right(@work_level3, 1) <= right(@input_work_level3, 1)
 as '''2A'' >= ''1C''',

 ( cast( left(@work_level4, length(@work_level4)-1) as unsigned )
   >=
   cast( left(@input_work_level4, length(@input_work_level4)-1) as unsigned )
 )
 and
 right(@work_level4, 1) <= right(@input_work_level4, 1)
 as '''2C'' >= ''15B'''
;

would result : 

+---------------+---------------+--------------+---------------+
| '25C' >= '1C' | '2C' >= '15C' | '2A' >= '1C' | '2C' >= '15B' |
+---------------+---------------+--------------+---------------+
|             1 |             0 |            1 |             0 |
+---------------+---------------+--------------+---------------+

Upvotes: 0

Sashi Kant
Sashi Kant

Reputation: 13465

Yes it will return the desired rows, but do remember to add an inverted comma to the where clause::

SELECT * FROM employee WHERE work_level >= '1C'

Upvotes: 1

Related Questions