All Forums Extensibility
gpolanch 46 posts Joined 12/11
14 Feb 2012
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) );

dnoeth 4628 posts Joined 11/04
14 Feb 2012

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

 

Dieter

gpolanch 46 posts Joined 12/11
14 Feb 2012

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

 

You must sign in to leave a comment.