db2 and the strip function

[michael.bierenfeld]

Hello,

we are currently porting some sql programs from vm/vse to aix. On The
VSE Machine (DB2) the following is valid :

-lots of crap - but in the where clause of the statement :

WHERE
JOBDESC = (STRIP(:HV-JOB) CONCAT '/in')

The STRIP function is not existent on the AIX DB2. It can be replace by
ltrim and rtrim no problem. But I think creating an sql function strip
is easier. Does anynody know how to do that.

Regards

Michael

[Serge Rielau]

mi****************@web.de wrote:

Hello,

we are currently porting some sql programs from vm/vse to aix. On The
VSE Machine (DB2) the following is valid :

-lots of crap - but in the where clause of the statement :

WHERE
JOBDESC = (STRIP(:HV-JOB) CONCAT '/in')

The STRIP function is not existent on the AIX DB2. It can be replace by
ltrim and rtrim no problem. But I think creating an sql function strip
is easier. Does anynody know how to do that.

Regards

Michael

CREATE FUNCTION STRIP(arg VARCHAR(4000))
RETURNS VARCHAR(4000)
CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
RETURN LTRIM(RTRIM(arg))

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[GMavor]

You can use the code out of this function if you can't create it.
--
-- DB2 UDB UDF(User-Defined Function) Samples for Migration
--
-- 2003/07/18 Second Version FncStrp2.txt
-- First version was in FncStrip.txt
--
-- Name of UDF: STRIP (STR VarChar(4000), BLT VarChar(8), SC
VarChar(1))
--
-- Used UDF: None
--
-- Description: Remove leading or trailing character SC from STR
according to BLT.
-- BLT must be L(leading), T(trailing) or B(both).
--
-- Author: TOKUNAGA, Takashi
--
--------------------------------------------------------------------------
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
!