467,923 Members | 1,850 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,923 developers. It's quick & easy.

db2 and the strip function

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
  • viewed: 10160
Share:
2 Replies
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
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.

Similar topics

3 posts views Thread by alex.mcshane | last post: by
2 posts views Thread by Daniel M. Hendricks | last post: by
6 posts views Thread by rtilley | last post: by
4 posts views Thread by Steve | last post: by
6 posts views Thread by eight02645999 | last post: by
6 posts views Thread by Christoph Zwerschke | last post: by
4 posts views Thread by Poppy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.