WHERE clause in select statement

I have a 6 digits ID number. Take ABCDEF as an example (each letter representing a digit)
I need to select those numbers whose BC is equivalent to a 2 digit number like "16" and its DEF is greater than a 3 digit number like "017"...

please take note that only the 3 last digits are compared not the entire number.

how I can write these conditions under the WHERE clause of the statement to achieve what I explained above?

You will need to write a function, unless you're using ORD(),

Then simply query like this:

Code:

---

SELECT user_ID, userName
FROM db.table
WHERE LENGTH(username) = 6

AND ORD(SUBSTRING(username,1,1)) + ORD(SUBSTRING(username,2,2)) = 16

AND ORD(SUBSTRING(username,3,3)) + ORD(SUBSTRING(username,4,4)) + ORD(SUBSTRING(username,5,5)) >= 17

---

You probably don't want ORD(), and will have to define your own function for converting the characters into corresponding decimal values.

Quote:

---

Originally Posted by soroush saffari

I have a 6 digits ID number. Take ABCDEF as an example (each letter representing a digit)
I need to select those numbers whose BC is equivalent to a 2 digit number like "16" and its DEF is greater than a 3 digit number like "017"...

please take note that only the 3 last digits are compared not the entire number.

how I can write these conditions under the WHERE clause of the statement to achieve what I explained above?

---

Should work with this query