As an enhancement to Gopakumar's solution, I've added this:
with tmp as (select '123555408' tst from dual )
select
decode(replace(translate(lower(tst),'1234567890','AAAAAAAAAA'),'A',''),null,'NUMERIC','NON-NUMERIC')
from tmp
with tmp as (select '123555###' tst from dual )
select
decode(replace(translate(lower(tst),'1234567890','AAAAAAAAAA'),'A',''),null,'NUMERIC','NON-NUMERIC')
from tmp
This is because in Gopakumar's solution, there is the possibility that the
string to be evaluated may actually have the "#" sign in it... in his
translate command, the following nonsensical string: "123###" would pass as
a "NUMERIC" value because the translation of the string does not distinguish
between non-numeric characters which existed in the input and non-numeric
characters substituted later by the validation function.
In my example, I chose the uppercase letter "A" as my marker translation
character; I applied a "lower" function to the input string so that the
uppercase "A" would stand out as a substitution which occurred after the
string was inputted into the validation expression.
I also tinkered around with the Oracle regular expressions formulas such as
the following:
with tmp as (select '987abc' tst from dual )
select tst from tmp where regexp_like(tst, '[[:digit:]]{0}')
Perhaps I have the syntax wrong or misunderstood the definitions, but I
"think" this reads as: "match where the input string has "0" occurrences of
numeric digits... this query actually still returns a result, which
contradicts that. Can anyone offer some insight or additional explanation?
Thanks!
Rich Pascual
On Sat, May 7, 2011 at 8:20 AM, paras cme <paras....@gmail.com> wrote:
> you can simply check ascii value of each character in your string. if it is
> between 48 and 57 then it contains a number.
>
> Regards,
> Paras.
>
>
> On Sat, May 7, 2011 at 1:03 PM, Gopakumar Pandarikkal <
> pandarik...@gmail.com> wrote:
>
>> sorry when did not allow any function :(
>>
>>
>>
>> On Sat, May 7, 2011 at 1:01 PM, Gopakumar Pandarikkal <
>> pandarik...@gmail.com> wrote:
>>
>>> * * DECODE(*replace*(translate(variable,'1234567890','##########'),'#'),
>>> *NULL*,'NUMBER','NON NUMER') ='NON NUMER'
>>>
>>> this condition will check if your variable is non number
>>> regards
>>> gopakumar Pandarikkal
>>>
>>>
>>>
>>>
>>> On Fri, May 6, 2011 at 7:04 PM, mallikarjuna D
>>> <techinfoma...@gmail.com>wrote:
>>>
>>>> select case when
>>>> length(translate(string,'!1234567890','!'))-length(string) <0 then 'numeric
>>>> have'
>>>> else 'no numeric') from dual
>>>>
>>>> --
>>>> You received this message because you are subscribed to the Google
>>>> Groups "Oracle PL/SQL" group.
>>>> To post to this group, send email to Oracle-PLSQL@googlegroups.com
>>>> To unsubscribe from this group, send email to
>>>> oracle-plsql-unsubscr...@googlegroups.com
>>>> For more options, visit this group at
>>>> http://groups.google.com/group/Oracle-PLSQL?hl=en
>>>>
>>>
>>>
>>>
>>> --
>>> Gopakumar P.G.
>>>
>>>
>>>
>>>
>>>
>>
>>
>> --
>> Gopakumar P.G.
>>
>>
>>
>>
>> --
>> You received this message because you are subscribed to the Google
>> Groups "Oracle PL/SQL" group.
>> To post to this group, send email to Oracle-PLSQL@googlegroups.com
>> To unsubscribe from this group, send email to
>> oracle-plsql-unsubscr...@googlegroups.com
>> For more options, visit this group at
>> http://groups.google.com/group/Oracle-PLSQL?hl=en
>>
>
> --
> You received this message because you are subscribed to the Google
> Groups "Oracle PL/SQL" group.
> To post to this group, send email to Oracle-PLSQL@googlegroups.com
> To unsubscribe from this group, send email to
> oracle-plsql-unsubscr...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/Oracle-PLSQL?hl=en
>
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
