SQL UDF for LPAD (left-pad) giving unexpected results

Extensibility

SQL UDF for LPAD (left-pad) giving unexpected results

Hello,

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 

 SELECT prov_id, 

 SUBSTR('000000000000000000000',1,

    (3 - CHARACTER_LENGTH( TRIM(TRAILING FROM TRIM(LEADING FROM prov_id) ) )))

 || TRIM(TRAILING FROM TRIM(LEADING FROM prov_id) )

 from TEST_TABLE;

-- when calling the UDF, 70 is changed to 007, but should be 070

 SELECT prov_id,

 CASE WHEN CHARACTER_LENGTH( TRIM(TRAILING FROM TRIM(LEADING FROM prov_id) ) ) < 3

 THEN SUDF_LPAD(PROV_ID,3) ELSE PROV_ID END

 from TEST_TABLE;

CREATE FUNCTION SUDF_LPAD (in_string VARCHAR, out_string_len INT)

RETURNS VARCHAR

LANGUAGE SQL

CONTAINS SQL

DETERMINISTIC

SQL SECURITY DEFINER

COLLATION INVOKER

INLINE TYPE 1

RETURN SUBSTR('000000000000000000000',1,

 (out_string_len - CHARACTER_LENGTH( TRIM(TRAILING FROM TRIM(LEADING FROM in_string) ) )))

 || TRIM(TRAILING FROM TRIM(LEADING FROM in_string) );

2 REPLIES
N/A

Re: SQL UDF for LPAD (left-pad) giving unexpected results

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)

RETURNS VARCHAR(21)

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.

Dieter

Re: SQL UDF for LPAD (left-pad) giving unexpected results

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!

-Greg