# Thread: String + Decimal Concatenate

1. Registered User
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 06:15.

2. Registered User
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. 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
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. 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
•