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

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,

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

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'

---