Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2014
    Posts
    1

    Unanswered: varchar to integer conversion issue

    No lie....I'm a software engineer so I don't know SQL that well...and I need help...Please.

    We're doing an import of data from a partner in which a dollar amount is transmitted in varchar(10) form.

    The possible values are:

    . (no value, just the decimal place indicator)
    $100.00 (leading $ is an issue for me)

    I need to convert this to a value that I can compare, such as

    VALUE > 1000

    When I try and cast it I get all kinds of crazy problems
    When I do a DECIMAL(blah blah blah) it doesn't do the compare correctly

    any advice on how I might translate and compare these values would be greatly apprecitated...

    G

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    db2level and platform ?
    display the statement and complete error message with some details
    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

  3. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:
    Code:
    select 
      v
    , dec(nullif(xmlcast(
    xmlquery('fn:replace($s, "^[\$\s]*(\d*\.\d*)\s*$", "$1")' passing t.v as "s")
    as varchar(12)), '.'), 10, 2) d
    from table(values '.1', '2.', ' . ', '$100.00') t(v)
    
    V       D           
    ------- ------------
    .1              0,10
    2.              2,00
     .                 -
    $100.00       100,00
    Regards,
    Mark.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another way(expression).

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT v
         , DEC( NULLIF( TRIM( REPLACE(v , '$' , '') ) , '.' ) , 10 , 2 ) d
     FROM  (VALUES '.1' , '2.' , ' . ' , '$100.00') t(v)
    ;
    ------------------------------------------------------------------------------
    
    V       D           
    ------- ------------
    .1              0.10
    2.              2.00
     .                 -
    $100.00       100.00
    
      4 record(s) selected.

Tags for this Thread

Posting Permissions

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