Results 1 to 6 of 6
  1. #1
    Join Date
    May 2008
    Posts
    35

    Unanswered: String + Decimal Concatenate

    Query

    Code:
    select COLA || '|', - String
             COLB '|', - Decimal 
             COLC '|', - String
             COLD '|', - Decimal
             COLE - String
    from TABLEA
    Problem is since the decimal datatypes contain records which are numbers they gets converted to char as use char(colb)

    Sample Output before conversion
    Code:
    ABC 48.0000 POP 49.75000 OOO
    But when concatenate happens the number changes to like this

    Sample Output after conversion
    Code:
    ABC|000048.0000000|POP|000000049.00000000|OOO
    Should be
    Code:
    ABC|48.0000|POP|49.75000|OOO
    Since the 000 are not consistent i can't use a replace function.
    What i need is concatenate the record with Pipe Separated but the decimal values should be intact.
    Last edited by dinjo_jo; 12-17-09 at 07:15.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    change the data type and strip it. For instance strip(char(colb),l,'0')

    Dave

  3. #3
    Join Date
    May 2008
    Posts
    35
    would it remove the correct 000 which are after 48.0000

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    you can, also, use B instead of L, this would remove leading and trailing. Another way of doing this if say you want to retain 2 places after the decimal point would be to use a substr along with the strip of leading 0's.

    Dave

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    if say you want to retain 2 places after the decimal point
    STRIP( CHAR( DEC( colb , xx , 2 ) ) , L , '0' )

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation EZer

    Simply:

    Code:
    select COLA || '|' || varchar(COLB)  || '|' ||
           COLC || '|' || varchar(COLD)  || '|' || COLE
        from TABLEA
    Lenny

Posting Permissions

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