如何将字符串转换为整数并比较指定数字使用mysql？(how to convert string to integer and compare specify number use mysql?)

 我使用sql获取text值，并将text字符串转换为整数以比较相同的指定数字。 原谅我可怜的英语。 第1步，我使用这个sql获取text值。（结果是'8000mAh'，'2000mAh'） 
 
SELECT `product_id`, `text` 
FROM `jfw_product_attribute` 
WHERE `attribute_id` =3
 
 第2步，我使用sql将text字符串转换为整数来比较相同的指定数字:(我删除了最后三个str'mAh'） 
 
SELECT `product_id` ,
CASE
WHEN cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as  UNSIGNED int) <1000
THEN 1
WHEN 1000 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as  UNSIGNED int) <=2000
THEN 2
WHEN 2001 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as  UNSIGNED int) <=3000
THEN 3
WHEN 3001 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as  UNSIGNED int) <=4000
THEN 4
WHEN 4001 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as  UNSIGNED int) <=5000
THEN 5
WHEN 5001 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as  UNSIGNED int) <=8000
THEN 6
WHEN 8001 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as  UNSIGNED int)
THEN 7
END AS `filter_id`
FROM `jfw_product_attribute`
WHERE `attribute_id` =3
 
 我无法得到正确的filter_id ，我只是得到所有filter_id是2.Someone可以帮助我吗？非常感谢你。 

I use sql to get text value,and convert text string to integer to compare same specify numbers. Forgive my poor English. Step 1,I use this sql get text value.(The result is Like '8000mAh','2000mAh')
 
SELECT `product_id`, `text` 
FROM `jfw_product_attribute` 
WHERE `attribute_id` =3
 
Step 2,I use sql convert text string to integer to compare same specify numbers:(I delete the last three str 'mAh')
 
SELECT `product_id` ,
CASE
WHEN cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as  UNSIGNED int) <1000
THEN 1
WHEN 1000 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as  UNSIGNED int) <=2000
THEN 2
WHEN 2001 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as  UNSIGNED int) <=3000
THEN 3
WHEN 3001 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as  UNSIGNED int) <=4000
THEN 4
WHEN 4001 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as  UNSIGNED int) <=5000
THEN 5
WHEN 5001 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as  UNSIGNED int) <=8000
THEN 6
WHEN 8001 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as  UNSIGNED int)
THEN 7
END AS `filter_id`
FROM `jfw_product_attribute`
WHERE `attribute_id` =3
 
I cant get right filter_id,I just get all filter_id is 2.Someone can help me?Thank you very much.

原文：https://stackoverflow.com/questions/20735900