+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

db2 and the strip function

  1. 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


  2. Re: db2 and the strip function

    michael.bierenfeld@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

  3. Re: db2 and the strip function

    michael.bierenfeld@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

  4. Re: db2 and the strip function

    michael.bierenfeld@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

  5. Re: db2 and the strip function

    michael.bierenfeld@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

  6. Re: db2 and the strip function

    michael.bierenfeld@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

  7. Re: db2 and the strip function

    michael.bierenfeld@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

  8. Re: db2 and the strip function

    michael.bierenfeld@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

  9. Re: db2 and the strip function

    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
    !


  10. Re: db2 and the strip function

    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 Thread
Page 1 of 2 1 2 LastLast