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
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.
08-31-2011, 03:21 AM
Put them in a separate field and then do the same concatenation.
create table demo (
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.
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.
Powered by vBulletin® Version 4.2.2 Copyright © 2015 vBulletin Solutions, Inc. All rights reserved.