How to compare a user set variable in MySQL?
Yes, a derived table will do. The inner select block below is a derived table. And every derived table needs a name. In my case, xDerived
.
The strategy is to let the derived table cleanse the use of the column name. Coming out of the derived chunk is a clean column named num
which the outer select is free to use.
Schema
create table employee
( id int auto_increment primary key,
experience varchar(20) not null
);
-- truncate table employee;
insert employee(experience) values
('4-5'),('7-1'),('4-1'),('6-5'),('8-6'),('5-9'),('10-4');
Query
select id,experience,num
from
( SELECT id,experience,
CONVERT(SUBSTRING_INDEX(experience,'-',1),UNSIGNED INTEGER) AS num
FROM employee
) xDerived
where num>=7;
Results
+----+------------+------+
| id | experience | num |
+----+------------+------+
| 2 | 7-1 | 7 |
| 5 | 8-6 | 8 |
| 7 | 10-4 | 10 |
+----+------------+------+
Note, your @num
concept was faulty but hopefully I interpreted what you meant to do above.
Also, I went with 7 not 3 because all your sample data would have returned, and I wanted to show you it would work.
The AS num
instruction names the result of convert
as num
, not a variable named @num
.
You could repeat the convert
SELECT experience,CONVERT(SUBSTRING_INDEX(experience,'-',1),UNSIGNED INTEGER)
FROM employee
WHERE CONVERT(SUBSTRING_INDEX(experience,'-',1),UNSIGNED INTEGER) >= 3;
Or use a partial (derived) table (only one convert
)
SELECT experience,num
FROM (select experience,
CONVERT(SUBSTRING_INDEX(experience,'-',1),UNSIGNED INTEGER) as num
FROM employee) as partialtable WHERE num>=3;