Question 243805 in Mysql Question posted in
The official MySQL documentation can be found here.

how to perform arithmetic operation on string in MySQL?

I have values like below in dealer_price(type string) field:

| dealer_price |

| -------- |

| 1000.00|

| 5000.00|

| Contact for pricing|

| 10000.00|

I have queries something like below to perform arithmetic operation:

select (dealer_price + 10) AS cust_price FROM my_table

I want to check field_type of each row in query if field_type is string then display string only and is numeric then perform arithmetic operation. right now above query considering string as 0 and perform operation. basically want output like below using query:

| cust_price |

| -------- |

| 1010.00|

| 5010.00|

| Contact for pricing|

| 10010.00|
Tags:

2

Answers


  1. 0

    You could use a CASE expression here:

    SELECT CASE WHEN dealer_price REGEXP '^[0-9]+(.[0-9]+)?$'
            THEN CAST(CAST(dealer_price AS UNSIGNED) + 10 AS varchar(255))
            ELSE dealer_price END AS cust_price
FROM my_table;

    Note that all branches of a CASE expression need to have the same type. In this case, that type must be string/text. Therefore we need to first cast numeric values of dealer_price to integer, increment by 10, and then cast back to text.

    Also note that it is generally bad practice to mix numeric and non numeric data in the same column.

    Login or Signup to reply.
  2. 0

    You can use case statement to check the dealer_price value’s data type:

    SELECT
    CASE
        WHEN dealer_price REGEXP '^[0-9]*[.]{0,1}[0-9]*$' THEN dealer_price + 10
        ELSE dealer_price
    END AS cust_price
FROM my_table;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search