Web Analytics Made Easy -
StatCounter two datatypes in one field - CodingForum


No announcement yet.

two datatypes in one field

  • Filter
  • Time
  • Show
Clear All
new 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
    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
      how to do it in fastest way? im also using php for that


      • #4
        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, ...
        Be yourself. No one else is as qualified.


        • #5
          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
            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).
            Be yourself. No one else is as qualified.


            • #7
              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
                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.
                Be yourself. No one else is as qualified.