String + Decimal Concatenate

dinjo_jo · #1 (**permalink**) 12-17-09, 06:12

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.

dav1mo · #2 (**permalink**) 12-17-09, 06:27

change the data type and strip it. For instance strip(char(colb),l,'0')

Dave

dinjo_jo · #3 (**permalink**) 12-17-09, 06:38

would it remove the correct 000 which are after 48.0000

dav1mo · #4 (**permalink**) 12-17-09, 13:19

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

tonkuma · #5 (**permalink**) 12-17-09, 13:58

Quote:

if say you want to retain 2 places after the decimal point

STRIP( CHAR( DEC( colb , xx , 2 ) ) , L , '0' )

Lenny77 · #6 (**permalink**) 12-17-09, 14:11

Simply:

Code:

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

Lenny

Internet Services

Web Development

Digital Marketing

Consumer Tech