Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: Using wildcards to specify numeric values in SQL

    I am curious if there is a way to specify that the last three digits of a value you wish to retrieve are numeric.

    For example:

    Here is my SQL -

    SELECT max(id) FROM mytable WHERE id like '123456_%';

    The problem is that I need to make sure that the values that are retrieved after the underscore are numeric. For example, if the values 123456_001, 123456_002, 123456_003 and 123456_00A are present, then the value I need back is 123456_003. However, the value that is returned back is 123456_00A because of max function treats the A as "greater" than the 3.

    Is there a way to ensure that I only get back the values where the last 3 digits (or even better, the three digits immediately following the underscore) are numeric.

    I have tried using the following, but it still doesn't work:

    SELECT max(id) FROM mytable WHERE id < '123456_999';


    Thanks...

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Using wildcards to specify numeric values in SQL

    Try this:

    WHERE TRANSLATE(SUBSTR(id,-3),'#0123456789','?##########') = '###'

  3. #3
    Join Date
    Feb 2004
    Posts
    2

    thanks

    Thanks for the reply...

    One more question. Right now this just gets me back the max for all of the id column. But, I can't specify the like to match it to the id value. For example, I still need to make sure that the max value is only for the string that I specify. In other words, I still need to make sure that it is like the value I compare it to.

Posting Permissions

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