Skip to content

Instantly share code, notes, and snippets.

@BirgittaHauser
Last active March 25, 2022 14:55
Show Gist options
  • Save BirgittaHauser/42a2bb0afdb054dbaa0dace9be53fe9e to your computer and use it in GitHub Desktop.
Save BirgittaHauser/42a2bb0afdb054dbaa0dace9be53fe9e to your computer and use it in GitHub Desktop.
Check String for numeric values
Create Function YourSchema/Checknum (Parstring Varchar(32))
Returns Integer
Language Sql
Modifies Sql Data
Deterministic
Called On Null Input
Secured
Begin
Declare LocNum Decimal(31, 9) Not NUll Default 0;
Declare Continue Handler For SQLException Return -1;
Set LocNum = Cast(ParString as Dec(31, 9));
Return 0;
End;
Label On Routine YourSchema/Checknum(Varchar())
Is 'Check Numeric Values';
Comment On Parameter Routine YourSchema/Checknum (Varchar())
(Parstring Is 'String to be checked');
commit;
/* Test */
With x (String) as (Values('12345'), ('-32,456'), ('0,23456'), ('-0,1000'), ('ABCD'), ('XYU'))
Select String, CheckNum(String)
from x;
@GlennGundermann
Copy link

In case anyone gets an error when trying to create this function like I did:
The Secured attribute requires authority to Database Security Administrator Functions (QIBM_DB_SECADM).
Our system has the function usage for this as:
Default authority = *DENIED
*ALLOBJ specify authority = *NOTUSED
You have four options:

  1. Comment out the Secured attribute.
  2. Add yourself as an authorized user to the function usage. CHGFCNUSG FCNID(QIBM_DB_SECADM) USER(user-name) USAGE(*ALLOWED)
  3. Change the default authority to *ALLOWED. CHGFCNUSG FCNID(QIBM_DB_SECADM) DEFAULT(*ALLOWED)
  4. If you have *ALLOBJ authority, change the *ALLOBJ special authority to *USED. CHGFCNUSG FCNID(QIBM_DB_SECADM) ALLOBJAUT(*USED)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment