This content has been marked as final. Show 7 replies
Hi, Raghu,1 person found this helpful
You can do it using regular expressions:
but this job can be done more efficiently without using regular expressions:
SELECT * FROM test_regexp WHERE REGEXP_LIKE ( str , '^[ 0-9]+$' ) ;
Edited by: Frank Kulash on Dec 1, 2010 11:23 AM
SELECT * FROM test_regexp WHERE str IS NOT NULL AND TRANSLATE ( str , 'x 0123456789' , 'x' ) IS NULL ;
this should work:1 person found this helpful
select sysdate from dual where regexp_like(' 3 9234 2 ','^([[:digit:]]|[[:blank:]])+$');
Or1 person found this helpful
select * from test_regexp where rtrim (str, ' 0123456789') is null /
Thanks for your valuable answers, if the space is between string then that string is not returned.
The source data is in external table, while loading from external table it is not selecting data if space is between strings which has only digits and spaces.
If I select from normal table, rtrim and regexp_like works fine even if space is in between.
insert into test_regexp values ('987 654 ');
How can we solve the issue if space is between string.
Edited by: Raghu on 2 Dec, 2010 10:56 AM
check this link1 person found this helpful
pass your values in function as ISNUMERIC(replace(mycolumn,' '))
try the following:
where regexp_like(src,'^[ 0-9 ]+([ 0-9 ]+)$');
Edited by: pusala on Dec 2, 2010 12:09 PM
Edited by: pusala on Dec 2, 2010 12:11 PM
while loading from external table it is not selecting data if space is between strings which has only digits and spaces.May be there are some unknown characters in the data as well.
Can you post the outcome of a row in question, like
select str, dump(str) from your_external_table where str_presumably_contains_digits_and_spaces