Results 1 to 3 of 3

Thread: WHERE clause in select statement

  1. #1
    Join Date
    Feb 2011
    Posts
    1

    Arrow 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?

  2. #2
    Join Date
    Apr 2011
    Location
    /ramdisk/
    Posts
    6
    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.

  3. #3
    Join Date
    May 2011
    Posts
    2
    Quote Originally Posted by soroush saffari View Post
    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
    Attached Images Attached Images

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •