Query to convert string data to int data to get max of value

Question

I using CAST and CONVERT sintax to convert data like this picture:

enter image description here

this is my query:

SELECT MAX(CONVERT(INT, kode_walimurid)) as idmaks FROM walimurid

The error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT, kode_walimurid)) as idmaks FROM walimurid LIMIT 0, 25' at line 1

you could make substring of kode_walimurid and get get rid of WM string, if it´s always the same pattern — Tommy
– Tommy, Commented Jul 27, 2017 at 10:52
@Qirel he wants to convert the column to int. int is not the columnname — Matthias Burger
– Matthias Burger, Commented Jul 27, 2017 at 10:53
@MatthiasBurger dev.mysql.com/doc/refman/5.7/en/… There's nothing INT in the manual, and he's getting an error right at INT — Qirel
– Qirel, Commented Jul 27, 2017 at 10:57

Gordon Linoff · Accepted Answer · 2017-07-27 11:41:39Z

5

You can use "silent" conversion, if you like:

SELECT MAX(kode_walimurid + 0) as idmaks
FROM walimurid;

This implicit conversion does not return an error, but it will always return 0, because the string starts with a non-digit character.

Presumably, though, you actually want the number that starts at position 3:

SELECT MAX(SUBSTR(kode_walimurid, 3) + 0) as idmaks
FROM walimurid;

Here is an example on Rextester.

edited Jul 27, 2017 at 11:41

answered Jul 27, 2017 at 10:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

7 Comments

ujang7654321 ujang7654321 Over a year ago

the result always "0"

Gordon Linoff Over a year ago

@ujang7654321ujang7654321 . . . That's why I offered the second option.

ujang7654321 ujang7654321 Over a year ago

i try the second option.. still 0

Gordon Linoff Over a year ago

@ujang7654321ujang7654321 . . . If I say position 3 in the text, I should implement the same in the code.

ujang7654321 ujang7654321 Over a year ago

Should I use CAST or CONVERT? but using it i have still error the same

|

Bibhudatta Sahoo · Accepted Answer · 2017-07-27 11:05:51Z

0

Try with CAST() mysql function check here function_cast
Like

SELECT MAX(CAST(kode_walimurid AS UNSIGNED)) as idmaks FROM walimurid

Edit:- Try to use user define function check this

edited Jul 27, 2017 at 11:05

answered Jul 27, 2017 at 10:54

Bibhudatta Sahoo

4,9142 gold badges31 silver badges53 bronze badges

2 Comments

ujang7654321 ujang7654321 Over a year ago

result "0", i dont know why

Bibhudatta Sahoo Over a year ago

Check the user define function on the above link.

Collectives™ on Stack Overflow

Query to convert string data to int data to get max of value

2 Answers 2

7 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related