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

Dale K
  • 25,246
  • 15
  • 42
  • 71
  • @DaleBurrell it is showing error i am using mysql 5.1 –  Feb 13 '19 at 06: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` –  Feb 13 '19 at 06: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. – Dale K Feb 13 '19 at 06:32
  • Possible duplicate of [Cast from VARCHAR to INT - MySQL](https://stackoverflow.com/questions/12126991/cast-from-varchar-to-int-mysql) – Thomas G Feb 13 '19 at 06:34
  • @dheerajkumar I have added demo to my answer. Have a look into that. – Suraj Kumar Feb 13 '19 at 06:36

2 Answers2

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

Mano
  • 780
  • 3
  • 15
  • 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` –  Feb 13 '19 at 06:29
  • Can you tell me the mySQL version – Mano Feb 13 '19 at 06:30
  • i am using version 5.1 –  Feb 13 '19 at 06:33
  • 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). – Mano Feb 13 '19 at 06: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

Suraj Kumar
  • 5,547
  • 8
  • 20
  • 42
  • 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 –  Feb 13 '19 at 06:16