Solved

Need assistance regarding decoding values.

Posted on 2008-06-18
19
597 Views
Last Modified: 2013-12-18
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.
0
Comment
Question by:srikanthradix
  • 9
  • 7
  • 2
  • +1
19 Comments
 
LVL 16

Assisted Solution

by:Milleniumaire
Milleniumaire earned 50 total points
ID: 21814351
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
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 150 total points
ID: 21814356
Try:

...

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

...

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21814389
regular expressions are available in 10g and make stripping non-numeric characters very easy


select REGEXP_REPLACE(:useral, '[^1234567890]', NULL) from dual
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21814404
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 21814462
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
 

Author Comment

by:srikanthradix
ID: 21814664
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 21814709
sorry, I misread the question.

try this...


"decode(replace(translate(:USERAL ,'0123456789','0000000000'),'0',null),null,:USERAL,null)"
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 300 total points
ID: 21814713
and to accept decimal points...


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

Author Comment

by:srikanthradix
ID: 21814780
Hi sdstuber,

I tried to insert 10989999.23 it came out as null, other than that everything looks good. Please suggest.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 73

Expert Comment

by:sdstuber
ID: 21814805
was that with the first or the second one?
0
 

Author Comment

by:srikanthradix
ID: 21814810
sdstuber,

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

0
 

Author Comment

by:srikanthradix
ID: 21814862
Hi sdstuber,
I have completed testing. Everything looks perfect, Can you please explain me the syntax?
0
 

Author Closing Comment

by:srikanthradix
ID: 31468432
Hi All,
I have assigned points based on which one is perfect.

sdstuber,
Can you please explain me the syntax.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21814991
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 21815003
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
 

Author Comment

by:srikanthradix
ID: 21815285
Is there any way to avoid the multiple .......'s ?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21815318
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
 

Author Comment

by:srikanthradix
ID: 21815611
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 21816256
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now