clean up dirty data and create function 1

[kokiri]

Please help....

I have dirty data need to clean up before I can calculate. First 2 digits are hours and last 2 digits are minutes. This column's data type is in varchar2 and this is how data is stored:
1. 1234
2. null
3. abcdefg
4. 12

What I would like to do is that if the field is not numeric and not 4 characters, I would like to display them as null.

I would like to create a function and use it several columns if I can.

Please help? Thanks bunch in advance!

 

[SantaMufasa]

Kokiri,

Here is a function, a sample run, and its output:

create or replace function check4digits (x in varchar2) return varchar2 is
begin
	if x is null or length(x) <> 4 then
		return null;
	end if;
	declare
		y number;
	begin
		y := x;
	exception
		when others then
			return null;
	end;
	return x;
end;
/

select 'Original value: ['||val||']. Return value: ['||check4digits(val)||'].' a
from tt_31

Input vs. Return values
---------------------------------------------
Original value: [1234]. Return value: [1234].
Original value: []. Return value: [].
Original value: [abcdefg]. Return value: [].
Original value: [12]. Return value: [].

4 rows selected.

Let me know if this is what you wanted.

Dave
Sandy, Utah, USA @ 20:10 GMT, 13:10 Mountain Time

 

[kokiri]

Hi Dave,

Thanks for your response and it works. Can you explain your logic please. I've tried to create a similar function but I've received an error with invalid number.

Again, thanks for your reply.

Di

 

[SantaMufasa]

Di,

My function's logic DEPENDS upon receiveing the same error that yours receives. Here is my code that DEPENDS upon receiving the error:

declare
y number;
begin
y := x;
exception
when others then
return null;
end;

...notice that I assign a possibly non-numeric value, "x", to "y", a numeric field. If/when "x" is non-numeric, the assignment causes a potentially fatal run-time error, but my EXCEPTION routine "handles" the error. "WHEN OTHERS" means "WHEN any error occurs, THEN do the following"; "the following" in this case, is to "RETURN NULL". Then, because my exception handler is in a sub-block, control from that sub-block drops to the outer-block environment, and continues processing.

Did that answer your question, or do you still have other questions?

Dave
Sandy, Utah, USA @ 22:03 GMT, 15:03 Mountain Time

 

[sem]

One more suggestion, in fact the same as Daves, but with deeper validation:

create or replace function getDate (x in varchar2) return varchar2 is
y Date;
begin
y := to_date(x,'hh24mi');
return x;

exception
when others then
return null;
end;

Note, that in the case when length(x)<4, 2 first digits are treated as hours, so you may either accept this behaviour or check length and process it manually.

Regards, Dima

 

[kokiri]

Thanks Dave and Dima for your reply and explanations.
When I create my function I use number rather than varchar2 for IN and it got chocked on non numeric data.

You guys are awesome.

Thanks for your help.

Di