View Full Version : two datatypes in one field

08-30-2011, 06:35 PM
hi is it possible to use two datatypes in one field in sql?
i want to edit the products_price = decimal(15,4) into product_price = char(3), decimal(15,4)

so the example output should be products_price = @800.0000

08-30-2011, 07:22 PM
no it isn't possible to have two data types for one field.

do you need the char to put the @ in front of the value? You'd merely need to use CONCAT to do that.

08-30-2011, 07:38 PM
how to do it in fastest way? im also using php for that

Old Pedant
08-30-2011, 09:03 PM
Leave it in PHP, I'd say. If you *always* put the @ there only for display on a web page.

But if you think you need to do it in MySQL you could:

SELECT CONCAT('@',FORMAT(product_price,4)) AS price, ...

08-31-2011, 02:31 AM
hi thanks for your reply, but there are 2 characters involve in a products_price
sample output1: @800.0000
sample output2: +900.0000

how can i execute that? thanks.

Old Pedant
08-31-2011, 03:21 AM
Put them in a separate field and then do the same concatenation.

create table demo (
prefix char(1),
product_price decimal(15,4)
insert into demo values( '@', 800.0000 ), ('+', 900.0000 );

SELECT CONCAT( prefix, FORMAT(product_price,4)) AS price, ...

(or do the concatenation in PHP, of course).

08-31-2011, 03:57 AM
hi old pendant, actually the characters are in currency symbol. i have products that needs two currencies.

for example product1 = S$800.0000
product2 = US$ 900.0000

the existing site has a product_price of 800 and 900 only. i already created field 'currency_code' that will handle different currencies for each products to use in concatenation. so i need to manually specify the currency code in sql database for which products need to set as S$ or US$ and then will be called in the php page.

Old Pedant
08-31-2011, 04:02 AM
Okay, so you use CHAR(3) or VARCHAR(10) or whatever for currency_code, right? So just use that instead:

SELECT CONCAT( currency_code, FORMAT(product_price,4)) AS price, ...

Aren't you making this too hard? I don't understand what the difficulty is.