finding if a string contains number using regexp_replace

Question

I want to find if a string is a pure string using regexp_replace only. If it is not a pure string, then designate the string as XX; Else, use the string.

For eg: If a string is 'A1', then since there is a number, it is not a pure string and the outout would be XX. If the string is AB, there is no number or anything other than an aplha, then use AB.

Note: This needs to be done in a wierd requirement only with regexp_replace. I know how this is done using regexp_like or translate etc. But, i would like to do it with regexp_replace only.

possible duplicate of [check if "it's a number" function in Oracle](http://stackoverflow.com/questions/5082176/check-if-its-a-number-function-in-oracle) — Ben, Sep 11 '14 at 15:23
The question is not a duplicate as I want the solution using only regexp_replace and the question was framed as such. The one you pointed to contains lots of solutions, but none with regexp_replace. — Casey, Sep 11 '14 at 18:24
It contains a solution with regexp_replace; the second one down.... — Ben, Sep 11 '14 at 18:26
I still don't see it from that link ! I see solutions using regexp_instr,regexp_like and the traditional oracle builtin functions such as translate or a custom function, but not regexp_replace. I did a search for the keyword in that page and did not find any. Am I missing something that you are able to view ? — Casey, Sep 11 '14 at 20:39

score 0 · Answer 1 · edited Sep 11 '14 at 15:49

0

I don't know about Oracle regexp_replace method but a 'generic' regular expression would be: regex = [a-z][A-Z] // Only alphabetical characters , upper case or lower case

edited Sep 11 '14 at 15:49

David Arenburg

91,361
17
137
196

answered Sep 11 '14 at 15:23

Grégory Vorbe

374
1
8

score 0 · Accepted Answer · answered Sep 11 '14 at 15:54

0

Thanks to all. With some R&D, I was able to find the solution which works as well.

select case when length('A1') = length(trim(regexp_replace('A1','[[:digit:]]',' '))) then 'string'
else 'alphanumeric'
end from dual

answered Sep 11 '14 at 15:54

Casey

213
1
7
17

score -1 · Answer 3 · edited Sep 11 '14 at 15:48

create table regexp_replace_example(test_string varchar2(100) not null);

insert into regexp_replace_example(test_string) values ('A1');
insert into regexp_replace_example(test_string) values ('AB');

select test_string, case 
when regexp_replace(test_string,'[^a-zA-Z]') != test_string then 'XX' 
else test_string 
end as output_value
from regexp_replace_example;

drop table regexp_replace_example;

finding if a string contains number using regexp_replace

3 Answers3