This content has been marked as final. Show 7 replies
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 ;
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
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