Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New Coder
    Join Date
    Aug 2011
    Posts
    25
    Thanks
    5
    Thanked 0 Times in 0 Posts

    two datatypes in one field

    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

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #3
    New Coder
    Join Date
    Aug 2011
    Posts
    25
    Thanks
    5
    Thanked 0 Times in 0 Posts
    how to do it in fastest way? im also using php for that

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,184
    Thanks
    75
    Thanked 4,339 Times in 4,305 Posts
    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:
    Code:
    SELECT CONCAT('@',FORMAT(product_price,4)) AS price, ...
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Aug 2011
    Posts
    25
    Thanks
    5
    Thanked 0 Times in 0 Posts
    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.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,184
    Thanks
    75
    Thanked 4,339 Times in 4,305 Posts
    Put them in a separate field and then do the same concatenation.

    Code:
    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).
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    New Coder
    Join Date
    Aug 2011
    Posts
    25
    Thanks
    5
    Thanked 0 Times in 0 Posts
    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.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,184
    Thanks
    75
    Thanked 4,339 Times in 4,305 Posts
    Okay, so you use CHAR(3) or VARCHAR(10) or whatever for currency_code, right? So just use that instead:
    Code:
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •