Are there any doc's which discuss SQL UDF's? (Especially the various qualifiers in the CREATE FUNCTION statement). I am trying to determine why my regular SQL works as expected, but when you put the same code into an SQL UDF, it returns unexpected results. Any insight would be greatly appreciated.
-- this regular SQL works, 70 is changed to 070
(3 - CHARACTER_LENGTH( TRIM(TRAILING FROM TRIM(LEADING FROM prov_id) ) )))
|| TRIM(TRAILING FROM TRIM(LEADING FROM prov_id) )
-- when calling the UDF, 70 is changed to 007, but should be 070
CASE WHEN CHARACTER_LENGTH( TRIM(TRAILING FROM TRIM(LEADING FROM prov_id) ) ) < 3
THEN SUDF_LPAD(PROV_ID,3) ELSE PROV_ID END
CREATE FUNCTION SUDF_LPAD (in_string VARCHAR, out_string_len INT)
SQL SECURITY DEFINER
INLINE TYPE 1
(out_string_len - CHARACTER_LENGTH( TRIM(TRAILING FROM TRIM(LEADING FROM in_string) ) )))
|| TRIM(TRAILING FROM TRIM(LEADING FROM in_string) );
You have to add the correct length to both your input and return VARCHAR, as VARCHAR = VARCHAR(1), e.g.
CREATE FUNCTION SUDF_LPAD (in_string VARCHAR(21), out_string_len INT)
I just wonder why "RETURNS VARCHAR" actually returns more than a single character?
And instead of nested TRIM(TRAILING(TRIM(LEADING))) better use TRIM(BOTH).
Details can be found in the "SQL Data Definition Lnaguage" manual.
Thanks for the quick response Dieter! Yes, setting the size worked!
Also, thanks for the TRIM(BOTH) tip. That will help to make the code a bit more readable!