Join Number & Text Columns Into One Column

[accessfever]

i have a database and i join two columns (one is currency in text and another is number in double ) into one column. My command is very simple: NUMBER & CURRENCY. However, i tried to manupilate the number column to MUST show two decimal places in Standard format plus the currency but not working.

First example, the currency is EUR and the number is 34.56667. The new field will show 34.56667EUR but i want 34.57EUR.

Second example, the currency is CHF and the number is 34.5. The new field will show 34.5EUR but I want 34.50EUR

Is it a way to manupliate the data???

[Mahesh]

You can achieve this by executing query :

ALTER TABLE `test`.`currency` CHANGE `value` `value` DOUBLE(4,2) NULL ;

Also through GUI give for double column give length as (4,2) this will do the needful.

[peterlaursen]

As far as I remember MySQL will cast values to binary strings before comparison in such case. So you will need to use string functions (http://dev.mysql.com/doc/refman/5.0/en/string-functions.html) what seems quite tricky (= almost hopeless) here or use or cast to a numeric type that can be round()ed.

Basically I also think the design does no seem to look very good. In MySQL it is common to ue a decimal(19,4) type for currency/money data and with FLOATING points variables you have the precision problem as described here: http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html .. so it is pretty much uncertain if a match will always occur when it should.

Can't you use DECIMAL types for both columns? Or at least cast to DECIMAL before the match performed by JOIN the JOIN like

Code:

JOIN .. ON (cast(col1 as decimal(19,4)) = (cast(col2 as decimal(19,4))

(note that I never tried something like it myself before so the syntax may not be correct or possible inside JOIN – so a little experimentation may be required).

But idea is shown here in its most simple form:

Code:

SELECT ROUND(CAST(1.1111111 AS DECIMAL(19,4)),2)

. First the original value is cast to a number (decimal) with 4 decimals that is next rounded to 2 decimals. Also always do this rounding as the very last step to avoid rounding errors. Using 4 decimal precision internally is a de-facto standard with financial systems that should return 2 decimals. That ensures 2 additional decimal positions to catch/eliminate/absorb accumulated rounding errors (what is normally sufficient with such systems).

If we shall comment further we will need the CREATE TABLE statements and the query. Also understand that this is beyond normal product support so helping here will not be first priority. This is more a (My)SQL question than a SQLyog question

[Mahesh]

We may have totally misunderstood. You used the term 'join' so we understood that you were JOINing in the SQL meaning of the word. Now I rather think that you are concatenating two values. That is simple – just use the ROUND() and CONCAT() functions:

Code:

SELECT CONCAT(currency,' ',ROUND(`value`,2)) AS result FROM currency;

what would return like

Code:

result
———
EUR 35.57
USD 35.00
CHI 34.57

[peterlaursen]

Just a link: http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html says

The FLOAT and DOUBLE data types are used to represent approximate numeric data values

… and … 

The DECIMAL and NUMERIC data types are used to store exact numeric data values

[Author]

Posts