AW: Scan for a character less than X'40

Hi,

SQL does not have a scalar function to do so, but you easily can write your
own function.
I just wrote an UDF that scans for characters < x'00' and returns the
position of the first occurrence otherwise it returns 0:

CREATE FUNCTION MySchema/InvalidHex (
ParString VarCHAR(1024) )
RETURNS Integer
LANGUAGE SQL
SPECIFIC MySchema/InvalidHex
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
BEGIN

Declare Pos Integer Default 1;
Declare Len Integer;

Set ParString = Trim(ParString);
Set Len = Length(ParString);

If Len <= 0 then Return 0;
End If;

While Pos <= Len Do
If Substr(ParString, Pos, 1) < x'40' Then Return Pos;
End If;
Set Pos = Pos + 1;
End While;

Return 0;
END ;

This function can be used as follows:
Select InvalidHex(MyCharField)

From MyTable

Where InvalidHex(MyCharField) > 0;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von John McKee
Gesendet: Thursday, 22. September 2011 17:25
An: Midrange Systems Technical Discussion
Betreff: Scan for a character less than X'40

Is there a way to scan a text field in a file for an embedded character less
than an EBCDIC blank? We retrieve text from vendors and post this to
accounts. I am going to modify the code to convert incoming characters less
than a space to a space. But, that doesn't address the potential of other
embedded characters already in the file. I can see how to write an RPG
program that would check each character, but wondered if SQL could be used -
just to see if any others are present. After more than three years of doing
this, we got hit with TWO x'15' embedded characters. I did not realize that
characters less than x'40' were not allowed. Receiving CPF5192 has taught
be otherwise. I just was looking for a (relatively) quick way to determine
if other invalid characters have been written to the file and those accounts
just have not been displayed yet.

I thought about using query, but I am not looking for a specific character -
just one less than x'40 if it exists at all.

John McKee