By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,589 Members | 2,255 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,589 IT Pros & Developers. It's quick & easy.

db2 and the strip function

P: n/a
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

Mar 2 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Mar 2 '06 #2

P: n/a
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
!

Mar 2 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.