Results 1 to 4 of 4

Thread: ltrim zero

  1. #1
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11

    Unanswered: ltrim zero

    we have a column vargraphic(4) that contains house-number
    we have occurances as
    0060
    this we want to translate to 60 = ok = char(int(col)) -> 60
    but what would be an easy way to detect if characters are present

    4MOR would return error
    but for for 04M we need 4M
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You can use STRIP function to "ltrim zero" ?

    strip(colname,L,'0')

    IBM DB2 9.7 Information Center for Linux, UNIX, and Windows
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    I believe this is a FAQ - provided that your house-numbers are expressed in Hindu-Arabic numerals (0-9).
    For non-european locales (far east etc) you might need different technique.
    Google for ISNUMERIC.
    If you are on v9.7 you can also use PL/SQL implementations of ISNUMERIC, or roll your own function (which can be in-lined).

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    sorry - missed that function.. (it is monday morning..)
    thanks for all update
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

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