Need assistance regarding decoding values.

Currently I am using DECODE functionality like this.
USERAL                     CHAR  "DECODE (RTRIM(:USERAL),'', NULL,'<>', NULL,NULL,NULL, RTRIM(:USERAL) )"
In this case i have identified only '' and '<>' as the special characters which when occurs the field will be decoded as NULL.

But what i need is "USERAL should accept only numbers, so any other special characters should be DECODED as NULL." I don't know the list of special characters that would come in the way. It seems like we are encountering special characters every time. So, i had to change the code every time. So, i need a functionality where i would check like if it isn't a number then that should be decoded as null. Please suggest.
srikanthradixAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
and to accept decimal points...


"decode(replace(translate(:USERAL ,'.0123456789','00000000000'),'0',null),null,:USERAL,null)"
0
 
MilleniumaireConnect With a Mentor Commented:
By special characters I assume you mean non-numeric characters not "non printable" characters like line feed, newline etc.

If you want to replace all non-numeric characters try using a combination of the TRANSLATE and replace functions e.g.

USERAL                     CHAR  "REPLACE(TRANSLATE (UPPER(RTRIM(:USERAL)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ<>!"....','XXXXXXXXXXXXXXXXXXXXXXXXXXX....'),'X',NULL)"

Basically, list all the characters you want to exclude from USERAL and TRANSLATE them to a specific character e.g. X.  Then use REPLACE to replace all 'X''s with NULL.  The only characters left should be the required ones.

The UPPER function is used to avoid having to list both upper and lower alpha characters.
0
 
MikeOM_DBAConnect With a Mentor Commented:
Try:

...
USERAL CHAR  "DECODE(Replace(Translate(RTRIM(:USERAL),'~0123456789','~'),'~',''),NULL,RTRIM(:USERAL),NULL)"
...

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sdstuberCommented:
regular expressions are available in 10g and make stripping non-numeric characters very easy


select REGEXP_REPLACE(:useral, '[^1234567890]', NULL) from dual
0
 
sdstuberCommented:
oh, you're using sqlloader.  I haven't tried to use regexp's  in a sql loader control file,  not sure if they are legal.  

try this and see....

USERAL CHAR  "REGEXP_REPLACE(:useral, '[^1234567890]', NULL)"
0
 
MikeOM_DBACommented:
PS: As I understand the question, the requirement is to reject (set to null) any field which contains non-numeric values and accept only those that contain all numerics.

The code I posted uses the replace() function to detect non-numeric values in a field and set the result to NULL if a non-numeric value exists, else it will set the result to the numeric value of the filed.

0
 
srikanthradixAuthor Commented:
Hi MikeOM..,

I have tested with your code. The USERAL is a field NUMBER(15,2). So, it should also accept decimals. And also, it is working fine except when i try to insert '~' it is inserting as '~' not NULL. For all others it seems to be working fine. Can you please give me the replacement syntax that satisfies the above conditions?
0
 
sdstuberCommented:
sorry, I misread the question.

try this...


"decode(replace(translate(:USERAL ,'0123456789','0000000000'),'0',null),null,:USERAL,null)"
0
 
srikanthradixAuthor Commented:
Hi sdstuber,

I tried to insert 10989999.23 it came out as null, other than that everything looks good. Please suggest.
0
 
sdstuberCommented:
was that with the first or the second one?
0
 
srikanthradixAuthor Commented:
sdstuber,

MY BAD.  the code with "." seems to be working fine.

0
 
srikanthradixAuthor Commented:
Hi sdstuber,
I have completed testing. Everything looks perfect, Can you please explain me the syntax?
0
 
srikanthradixAuthor Commented:
Hi All,
I have assigned points based on which one is perfect.

sdstuber,
Can you please explain me the syntax.
0
 
sdstuberCommented:
sure...
for
decode(replace(translate(:USERAL ,'.0123456789','00000000000'),'0',null),null,:USERAL,null)

working from the inside out...

translate(:USERAL ,'.0123456789','00000000000')

change all numeric characters 0-9 and "."  to 0's, this makes it easier to strip them out because now they are all the same.

so next step,  
replace(translate(:USERAL ,'.0123456789','00000000000'),'0',null)

change all "0" characters (which used to be all digits or decimal points) to NULL
this effectively strips them out,  so after the replace, the only characters left in the string will be the non-numeric characters.

that leaves...
decode(replace(translate(:USERAL ,'.0123456789','00000000000'),'0',null),null,:USERAL,null)"

this says check the remaining string after stipping all numeric characters.
If NULL then return the original string because if the string has nothing left in it, then
it must have been only numeric

If the string is not null, then that means it has non-numeric characters left,  so return NULL because we don't want it.






0
 
sdstuberCommented:
I will note one hole in the above.


"................"   will be accepted as a valid number.  because the all the decimal points will be replaced to 0 and then stripped.  leaving null so it will pass the test.
0
 
srikanthradixAuthor Commented:
Is there any way to avoid the multiple .......'s ?
0
 
sdstuberCommented:
here's a long way,  use instr to check if there is more than one decimal point

if the INSTR for the second one returns 0 then it's ok (there is either no decimal or just one)
so do the check as before.
If the INSTR returns anything other than 0 then return NULL


decode(instr(:USERAL,'.',1,2),0,
decode(replace(translate(:USERAL ,'.0123456789','00000000000'),'0',null),null,:USERAL,null), null)
0
 
srikanthradixAuthor Commented:
Wow.... Today I understand that even if somebody know the functions seperately, they've to know how to use them in practise/reality. That answer was absolutely graceful.
0
 
sdstuberCommented:
it'll come with time and experimenting.
I know what I know because of all the things I've tried and had fail on me and I keep trying to find better ways.  

Eventually I built a library of things in my head that did work.  :)

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.