Scaler Function: "Strip"

[alex.mcshane]

Hi - I would be grateful for any advice on the following.

Within DB2 for OS/390, the STRIP Scaler Function is available.
Its function is, for example, to remove leading zeros from a string.

For example: -
Strip(Char(Digits(v_cntrct_no_seed)),L,'0')

- v_cntrct_no_seed is defined as Integer
- Char and Digits return a character representation of a number with
leading zeros.
- STRIP removes 'L'eading zeros.
The Strip Scaler function does not appear to exist in UDB LUW v7 or
indeed v8.

Is there an equivalent command?

Thanks

[Tonkuma]

CREATE FUNCTION STRIP (STR VarChar(4000), BLT VarChar(8), SC
VarChar(1))
RETURNS VARCHAR(4000)
SPECIFIC STRIP_V2_3P
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
CASE upper(substr(BLT,1,1))
WHEN 'T' THEN
translate(rtrim(translate(Str, ' ' || SC, SC || ' ')), ' ' || SC, SC
|| ' ')
WHEN 'L' THEN
translate(ltrim(translate(Str, ' ' || SC, SC || ' ')), ' ' || SC, SC
|| ' ')
WHEN 'B' THEN
translate(ltrim(rtrim(translate(Str, ' ' || SC, SC || ' '))), ' ' ||
SC, SC || ' ')
END
!

This is included in "Sample UDFs for Migration"
http://www-128.ibm.com/developerwork...dfs/index.html

[Jan M. Nelken]

al**********@btinternet.com wrote:

Hi - I would be grateful for any advice on the following.

Within DB2 for OS/390, the STRIP Scaler Function is available.
Its function is, for example, to remove leading zeros from a string.

For example: -
Strip(Char(Digits(v_cntrct_no_seed)),L,'0')

- v_cntrct_no_seed is defined as Integer
- Char and Digits return a character representation of a number with
leading zeros.
- STRIP removes 'L'eading zeros.
The Strip Scaler function does not appear to exist in UDB LUW v7 or
indeed v8.

Is there an equivalent command?

Thanks

Search online documentation available at:

http://publib.boulder.ibm.com/infoce...help/index.jsp

for RTRIM and LTRIM scalar functions.
Jan M. Nelken

[Tonkuma]

RTRIM and LTRIM removes only blanks.
So, I swapped blanks and strip characters, then applied RTRIM and/or
LTRIM, then swapped again blanks and strip characters to return to
original string(with STRIPPED).