1. Registered User
Join Date
May 2008
Posts
35

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. Registered User
Join Date
Dec 2007
Location
Richmond, VA
Posts
1,328
change the data type and strip it. For instance strip(char(colb),l,'0')

Dave

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

4. Registered User
Join Date
Dec 2007
Location
Richmond, VA
Posts
1,328
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. Registered User
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. Registered User
Join Date
Jul 2009
Location
NY
Posts
963

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
•