0

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.

BadHorsie
  • 14,135
  • 30
  • 117
  • 191

2 Answers2

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).

Alma Do
  • 37,009
  • 9
  • 76
  • 105
  • if number is between the string or at last. Will this function work? – Maz I Nov 11 '13 at 10:38
  • @MazIqbal of course not. It just strips non-significant symbols when casting to certain data type, saving only leading valid symbols – Alma Do Nov 11 '13 at 10:41
0

you can use user defined function

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

Removing numbers from string in mysql

Community
  • 1
  • 1
Maz I
  • 3,664
  • 2
  • 23
  • 38