Convert String to integer and increment in MYSQL

Question

I am new to MySQL and i am storing a version number in a column called version which is a string.I want to convert it into integer and increment by one. For example if version = 2.2.1....By running a query i want to change it to 2.2.2 is there anyone who did similar queries?

I like the idea of a three column approach as suggested here stackoverflow.com/questions/22295871/… — Matt Jameson
– Matt Jameson, Commented Oct 26, 2018 at 10:51

ScaisEdge · Accepted Answer · 2018-10-26 11:09:24Z

1

You could use some string function

SELECT CONCAT(
    LEFT(your_column, LENGTH(your_column) - LOCATE('.', REVERSE(your_column))+1), 
        CAST(SUBSTRING_INDEX(your_column, '.', -1) AS UNSIGNED) +1
)

eg

SELECT CONCAT(
    LEFT("2.2.2", LENGTH("2.2.2") - LOCATE('.', REVERSE("2.2.2"))+1),  
        CAST(SUBSTRING_INDEX("2.2.2", '.', -1) AS UNSIGNED) +1
)

edited Oct 26, 2018 at 11:09

answered Oct 26, 2018 at 10:58

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

1 Comment

Greeshma Over a year ago

this query is giving result as 2.2..1

Collectives™ on Stack Overflow

Convert String to integer and increment in MYSQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related