-
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,
Soma
-
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'
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|