MySQL - Best way to extract integer from string

Question

I am looking for the best way to extract the integer from these strings:

Next Day
2 Days
3 Days
...
29 Days
30 Days
30+ Days

I could do this with substrings but I'm just wondering if there is a simpler function which would automatically attempt to convert the string to an integer (similar to PHP's intval() function, for example.

Alma Do · Accepted Answer · 2013-11-11 10:42:27Z

2

Use CAST()

SELECT CAST('30+ Days' as UNSIGNED)

-if data is signed, then, of course cast to SIGNED:

SELECT CAST('-30 Days' as SIGNED);

However, it will not be able to act like PHP's strtotime() or similar and will not convert Next Day to something meaningful. If you need to do that, use DATE_ADD() function (or similar).

edited Nov 11, 2013 at 10:42

answered Nov 11, 2013 at 10:36

Alma Do

37.4k10 gold badges81 silver badges106 bronze badges

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

2 Comments

M I Over a year ago

if number is between the string or at last. Will this function work?

Alma Do Over a year ago

@MazIqbal of course not. It just strips non-significant symbols when casting to certain data type, saving only leading valid symbols

Community · Accepted Answer · 2017-05-23 12:29:15Z

0

you can use user defined function

https://gist.github.com/slav123/4274088

Removing numbers from string in mysql

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Nov 11, 2013 at 10:40

M I

3,6822 gold badges27 silver badges38 bronze badges

Collectives™ on Stack Overflow

MySQL - Best way to extract integer from string

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related