How to write this query..?

[skdas]

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

[ssekhar]

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,

[skdas]

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';

[TomazZ]

Yes, Oracle does not know about regular expressions.
Yes, this will work.
Yes, there is another way:

select count(*) from t1
 where translate(substr(f1,2,3),'0123456789','9999999999')='999'