DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: How to write this query..?

  1. #1
    Join Date
    Jul 2002
    Posts
    205

    How to write this query..?

    I want to select field f1 from table t1.

    f1 is varchar(3). The first character is any character and second one must be a number between 0 to 9.

    I wrote as follows.

    select count(*)
    from T1
    where f1 like '_[0-9]%';

    But it does not work. Is it the correct one..?

  2. #2
    Join Date
    Aug 2002
    Posts
    19
    Hi,

    If the first character is any character, you don't need to worry about it. Primarily your objective is to ensure that the second character is a number and hence use substr -

    SELECT COUNT(*)
    FROM t1
    WHERE SUBSTR(f1,2,1) BETWEEN 0 AND 9;


    Regards,
    Soma

  3. #3
    Join Date
    Jul 2002
    Posts
    205
    My actual requirement is as follows.

    1. The field f1 is varchar(4)

    2. I have to select the first character what ever it may be. It may be char or digit.

    3. I have to select those records, where 2nd position to 4th position characters are number varying from 0 to 9.

    Is it that you are suggesting the query like below..? Or any other way is there..?

    SELECT COUNT(*)
    FROM t1
    WHERE SUBSTR(f1,2,1) BETWEEN '0' AND '9'
    AND SUBSTR(f1,3,1) BETWEEN '0' AND '9'
    AND SUBSTR(f1,4,1) BETWEEN '0' AND '9';

  4. #4
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Yes, Oracle does not know about regular expressions.
    Yes, this will work.
    Yes, there is another way:
    Code:
    select count(*) from t1
     where translate(substr(f1,2,3),'0123456789','9999999999')='999'
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

Posting Permissions

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


Click Here to Expand Forum to Full Width