db2 and the strip function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

michael.bierenfeld@web.de - 02 Mar 2006 14:42 GMT

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

Reply to this Message

Serge Rielau - 02 Mar 2006 15:09 GMT

> Hello,
>
[quoted text clipped - 13 lines]
>
> Michael

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

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

GMavor@gmail.com - 02 Mar 2006 19:04 GMT

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
!

Reply to this Message