Professional Web Applications Themes

missing strip function in DB2 8.1 -- remove leading zeros - IBM DB2

sujit <yhkumars> wrote: > I am using Db2 8.1. > > I want to remove the leading zeros from a decimal that is cast > to a char. Tried to use the STRIP function as per the > recommendations in other posts related to this topic. However, > I can't find this function in Db2 8.1. Wonder why it was removed. > Couldn't find any equivalent function in Db2 8.1 to achieve > the same result. LTRIM removes only leading blanks. > STRIP was a useful function where you could specify > the character to remove. Where did you have ...

  1. #1

    Default Re: missing strip function in DB2 8.1 -- remove leading zeros

    sujit <yhkumars> wrote:
    > I am using Db2 8.1.
    >
    > I want to remove the leading zeros from a decimal that is cast
    > to a char. Tried to use the STRIP function as per the
    > recommendations in other posts related to this topic. However,
    > I can't find this function in Db2 8.1. Wonder why it was removed.
    > Couldn't find any equivalent function in Db2 8.1 to achieve
    > the same result. LTRIM removes only leading blanks.
    > STRIP was a useful function where you could specify
    > the character to remove.
    Where did you have the STRIP function? It didn't exist in version 7.
    ([url]http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/doent.d2w/report?fn=db2v7s0frm3toc.htm[/url])

    So what were the parameters and semantics of the function?

    You could use this construct to remove leading '0's:

    values replace(ltrim(rtrim(replace(char(decimal(10.02, 31, 2)), '0', ' '))),
    ' ', '0')

    Or wrap it in a SQL function:

    CREATE FUNCTION strip(str VARCHAR(4000), c CHAR)
    RETURNS VARCHAR(4000)
    LANGUAGE SQL
    DETERMINISTIC
    NO EXTERNAL ACTION
    CONTAINS SQL
    RETURN REPLACE(LTRIM(RTRIM(REPLACE(str, c, ' '))), ' ', c)


    And if you want to use an additional parameter to determine whether the
    leading and/or trailing characters are to be removed:

    CREATE FUNCTION strip(str VARCHAR(4000), pos, CHAR, c CHAR)
    RETURNS VARCHAR(4000)
    LANGUAGE SQL
    DETERMINISTIC
    NO EXTERNAL ACTION
    CONTAINS SQL
    RETURN CASE UPPER(pos)
    WHEN 'L' THEN REPLACE(LTRIM(REPLACE(str, c, ' ')), ' ', c)
    WHEN 'R' THEN REPLACE(RTRIM(REPLACE(str, c, ' ')), ' ', c)
    ELSE REPLACE(LTRIM(RTRIM(REPLACE(str, c, ' '))), ' ', c)
    END



    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Stolze Guest

  2. #2

    Default Re: missing strip function in DB2 8.1 -- remove leading zeros

    Hi,

    I am only a novice here on this list, but wouldn't

    select cast('000001230.0' as DECIMAL(31,2)) from sysibm.sysdummy1

    suffice to remove the leading blanks from your string and convert the value
    to a DECIMAL? Or is your string too long?

    Regards

    Rudolf Bargholz

    "sujit" <yhkumars> schrieb im Newsbeitrag
    news:a64cf821.0307151546.5a26964fposting.google.c om...
    > I am using Db2 8.1.
    >
    > I want to remove the leading zeros from a decimal that is cast
    > to a char. Tried to use the STRIP function as per the
    > recommendations in other posts related to this topic. However,
    > I can't find this function in Db2 8.1. Wonder why it was removed.
    > Couldn't find any equivalent function in Db2 8.1 to achieve
    > the same result. LTRIM removes only leading blanks.
    > STRIP was a useful function where you could specify
    > the character to remove.
    >
    > db2 => select STRIP(CHAR(DECIMAL(VOLUME,31,2)),'L','0') from
    > i_item_master
    > select STRIP(CHAR(DECIMAL(VOLUME,31,2)),'L','0') from i_item_master
    > SQL0440N No authorized routine named "STRIP" of type "FUNCTION"
    > having
    > compatible arguments was found. SQLSTATE=42884
    >
    > db2 => select ROUTINENAME from syscat.routines where ROUTINESCHEMA
    > like '%SYSFUN%' and ROUTINENAME like '%STRIP%'
    >
    > ROUTINENAME
    > -----------
    >
    > 0 record(s) selected.
    >
    > Any other workarounds to this problem ?

    Rudolf Bargholz Guest

  3. #3

    Default Re: missing strip function in DB2 8.1 -- remove leading zeros

    You can download STRIP here:
    [url]http://www7b.software.ibm.com/dmdd/library/samples/db2/0205udfs/index.html[/url]


    Cheers
    Serge


    Serge Rielau Guest

  4. #4

    Default Re: missing strip function in DB2 8.1 -- remove leading zeros

    Serge Rielau wrote in message
    > You can download STRIP here:
    > [url]http://www7b.software.ibm.com/dmdd/library/samples/db2/0205udfs/index.html[/url]
    >
    Here's another examples of user defined function STRIP:
    (Example1)
    -- Description:
    -- Remove leading or trailing character SC from STR according to BLT.
    -- BLT must be L(leading), T(trailing) or B(both).
    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
    !

    (Example2) Omit the 3rd parameter. (Default value: Blank)
    --------------------------------------------------------------------------
    CREATE FUNCTION STRIP (STR VarChar(4000), BLT VarChar(8))
    RETURNS VARCHAR(4000)
    SPECIFIC STRIP_V2_P2
    LANGUAGE SQL
    CONTAINS SQL
    NO EXTERNAL ACTION
    DETERMINISTIC
    RETURN
    CASE upper(substr(BLT,1,1))
    WHEN 'T' THEN rtrim(Str)
    WHEN 'L' THEN ltrim(Str)
    WHEN 'B' THEN ltrim(rtrim(Str))
    END
    !
    Tokunaga T. Guest

Similar Threads

  1. Replies: 2
    Last Post: June 16th, 12:55 PM
  2. Disappering leading zeros
    By elvis is dead in forum Macromedia ColdFusion
    Replies: 0
    Last Post: May 13th, 11:23 AM
  3. CSV file - Leading Zeros
    By Paul Kraus in forum PERL Beginners
    Replies: 5
    Last Post: December 15th, 10:22 PM
  4. Insert leading zeros
    By Matt Palermo in forum PHP Development
    Replies: 2
    Last Post: July 16th, 12:48 AM
  5. CSV for Excel - Problem with Leading Zeros
    By Luke Zhang [MSFT] in forum ASP.NET General
    Replies: 0
    Last Post: June 26th, 01:34 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139