0

I am using MySQL and I want to convert a string to a number because I have to select the max no between them.

  • In my database I have two columns item code and item name, both are of varchar type
  • so the item code I want it to convert in int or double

This is my table in database

here I am writing query as SELECT MAX(itemcode) FROM ITEMMASTER it is giving me result as 603 I want it to give me 2402 as this one is the maximum

Any help or guidance will be appreciated

Improve this question
5
  • @DaleBurrell it is showing error i am using mysql 5.1 Commented Feb 13, 2019 at 6:24
  • this is the error i am getting check the manual that corresponds to your MySQL server version for the right syntax to use near 'int)) from ITEMMASTER' at line 1 Commented Feb 13, 2019 at 6:29
  • And as you add additional information through answering questions, please add that information directly to your question so that you question contains all the required information for an answer to be provided. Commented Feb 13, 2019 at 6:32
  • Possible duplicate of Cast from VARCHAR to INT - MySQL Commented Feb 13, 2019 at 6:34
  • @dheerajkumar I have added demo to my answer. Have a look into that. Commented Feb 13, 2019 at 6:36

2 Answers 2

Reset to default
2

You can try the following query.

select Max(cast(itemcode as UNSIGNED)) from ITEMMASTER

For your reference : https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#function_cast

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

hey i am getting error check the manual that corresponds to your MySQL server version for the right syntax to use near 'int)) from ITEMMASTER' at line 1
Can you tell me the mySQL version
i am using version 5.1
Unsigned numbers do not have the minus sign. Unsigned number can be only positive or zero (e.g. 123, 0). Signed numbers can be also negative (e.g. -42).
0

You can try MySQL CAST() function is as follows:

CAST(expression AS TYPE);

CREATE TABLE ITEMMASTERTest  (
    ItemCode VARCHAR(255),
    ItemName VARCHAR(255)
);

INSERT INTO ITEMMASTERTest (ItemCode,ItemName) VALUES ('2402', 'A');
INSERT INTO ITEMMASTERTest (ItemCode,ItemName) VALUES ('333', 'B');
INSERT INTO ITEMMASTERTest (ItemCode,ItemName) VALUES ('603', 'C');
INSERT INTO ITEMMASTERTest (ItemCode,ItemName) VALUES ('1001', 'C');

Select Max(CAST(ItemCode AS SIGNED)) FROM ITEMMASTERTest ;

You can check the live demo Here

Improve this answer

1 Comment

i have tried but it didn't work....can you help me with query which will give me max item code after conversion as i have also attached my db image

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.