Perform comparison(<=, >=) operation on alphanumeric string in mysql

I have a table named "Test" contains following data

Name

A-00011
A-00012
A-00102
A-00202


and I want to fetch all record that are '<150'.

'<150'

so anyone can help how to perform the <= and >= operation on alphanumeric string in mysql via query.

2 Answers
2

This can be done by substringing to remove the first two chars, then cast to unsigned int:

SELECT CAST(SUBSTRING("A-00012", 3) AS UNSIGNED)


Your query would look something like:

SELECT * FROM Test WHERE CAST(SUBSTRING(myColumn, 3) AS UNSIGNED) <= 150


SELECT *
FROM test
WHERE name < 'A-00150';


should work. (Assuming that's what you mean by "'<150'" and that all the names follow that pattern.)

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.