Re: non alpha characters cleaning strings ??
Pardon me for asking this newby question, but I don't have much experience with stored procedures.
The reason for this data transformation to remove non-alpha characters is because we have to map one table to another, on a continual basis
Basically, we'll be selecting everything from source-tab, manipulating the data a little, with string commands, then for each row, determine if 2 columns ( key columns in the target-tab) already exist in the target tab. If they don't exist, then insert the transformed row, if they do exist, then update the row.
We've been trying to do this with Mercator ( now called websphere transformation extender ).
We're running into issues with that, and I started thinking ( dangerous ), that this sounds like something that can be handled efficiently with a stored procedure.
Something like:
for each
select * from source-tab
newcol1=substr(oldcol1,1,6)
....
call another procedure or a different section of the same procedure to do the alpha thing
.....
if newcol1 exists in target
then update
else insert
end
1) Am I on the right track here, is this something easily handled efficiently with a stored procedure ?
2) And if so, can I get a basic sp script from someone to do this and provide the framework I could use to make the real
procedure ?
Any advice is appreciated, and again, sorry for the newbie question. If I had more time, I'd be glad to go to the books first, but I'm running out of the little time I had for this project.
Thank you,
Floyd
========================
-<<Floyd Wellershaus>>-
Database Administrator
Unix Administrator
email: fwellers@yahoo.com
Home: 703-430-0805
Cell: 703-477-6045
========================
http://www.one.org/
----- Original Message ----
From: Doug Lawry <lawry@nildram.co.uk>
To: informix-list@iiug.org
Sent: Thursday, March 29, 2007 8:06:35 AM
Subject: Re: non alpha characters cleaning strings ??
This would be more elegant:
if substr(p_char,i,1) matches "[a-zA-Z]" then
--
Regards,
Doug Lawry
www.douglawry.webhop.org
"Ben" <ben_informix@comcast.net> wrote in message
news:mailman.454.1174400313.10648.informix-list@iiug.org...
Real Ugly but it works and maybe easier than installing the datablade:
create procedure rm_nonalpha(p_char varchar(50,1))
returning varchar(50,1);define i, mlen smallint;
define my_return varchar(50,1);
let my_return = "";
let mlen = length(p_char);
for i = 1 to mlen
if substr(p_char,i,1) in ("a", "b", "c", "d", "e", "f", "g", "h", "i",
"j", "k", "l", "m", "n", "o", "p", "q", "r",
"s", "t", "u", "v", "w", "x", "y", "z", "A",
"B", "C", "D", "E", "F", "G", "H", "I", "J",
"K", "L", "M", "N", "O", "P", "Q", "R", "S",
"T", "U", "V", "W", "X", "Y", "Z") then
let my_return = my_return || substr(p_char,i,1);
end if;
end for;
return my_return;
end procedure;
execute procedure rm_nonalpha("ab-cDe'fg ^hi\\jk@l#m%");
----- Original Message -----
From: Floyd Wellershaus
To: Obnoxio The Clown
Cc: informix-list@iiug.org
Sent: Saturday, March 17, 2007 12:29 PM
Subject: Re: non alpha characters cleaning strings ??
Hmm. I see the regexp.1.0. It is only for SunOS and Windows. Kinda leaves me out
in the cold with AIX.
========================
-<<Floyd Wellershaus>>-
Database Administrator
Unix Administrator
email: fwellers@yahoo.com
Home: 703-430-0805
Cell: 703-477-6045
========================
http://www.one.org/
----- Original Message ----
From: Obnoxio The Clown <obnoxio@serendipita.com>
To: Floyd Wellershaus <fwellers@yahoo.com>
Cc: informix-list@iiug.org
Sent: Saturday, March 17, 2007 11:21:29 AM
Subject: Re: non alpha characters cleaning strings ??
Floyd Wellershaus said:
> You mean the regexp blade is free ?
Yep.
--
Bye now,
Obnoxio
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list