Convert a string with operation to int using sql query

Question

persons is a tinytext field and can contain "4 + 2", "4+2", "4 +2", "5" or "" and so on. I would like to select and int like 6, 6, 6, 5 and 0 from that MySQL 5.6 Table.

Tried this without success:

SELECT CAST(persons AS INT) FROM Table
SELECT CONVERT(INT, persons ) FROM Table

With 2K rep you should already know [how-to-ask](https://stackoverflow.com/help/how-to-ask) a question on SO. — Paul Spiegel, Mar 21 '19 at 17:01
so you want magic to happen? how many operations do you have in those strings? what is the list of operations? `-+/*` do you want to support braces? — Alex, Mar 21 '19 at 17:05
No it's only + Operation or just a number or empty. It's an old database that needs to be migrated. Not looking for magic, but you never know if there is something you missed. If there is no solution will do it on server side. — Mike, Mar 21 '19 at 17:07
Possible duplicate of [Summing a comma separated column in MySQL 4 (not 5)](https://stackoverflow.com/questions/6311530/summing-a-comma-separated-column-in-mysql-4-not-5) — Alex, Mar 21 '19 at 17:12

score 3 · Accepted Answer · answered Mar 21 '19 at 17:26

3

If + is the only operator and it appears once, then:

select (case when col like '%+%'
             then substring_index(col, '+', 1) + substring_index(replace(col, ' ', ''), '+', -1)
             else col + 0
        end) as added_value

answered Mar 21 '19 at 17:26

Gordon Linoff

1,242,037
58
646
786

Works! I knew there must be a way. Thanks! – Mike Mar 21 '19 at 17:35

Zaynul Abadin Tuhin · Answer 2 · 2019-03-21T17:33:02.717

1

use SUBSTRING_INDEX

 select SUBSTRING_INDEX(col  , "+", 1)+ SUBSTRING_INDEX(col  , "+", -1) as col1
   from cte where col like '%+%'
  union all
  select SUBSTRING_INDEX(col  , "+", 1) from cte where col not like '%+%'

output

the upper solution will work only for your sample data demo link

edited Mar 21 '19 at 17:33

answered Mar 21 '19 at 17:01

Zaynul Abadin Tuhin

31,407
5
33
63

I'm afraid this won't work because "persons" can also be "4+2", "4 + 2" or "4+ 2" and so on. I need something like an operation interpretation. – Mike Mar 21 '19 at 17:04
@Mike . . . This will also return `10` instead of `5` for `'5'`. – Gordon Linoff Mar 21 '19 at 17:27
@GordonLinoff i just updated answer for only sample data – Zaynul Abadin Tuhin Mar 21 '19 at 17:34

score 0 · Answer 3 · edited Mar 21 '19 at 17:12

0

What database are you using? You may need to use something db specific. e.g. in oracle you can do:

select dbms_aw.eval_number ('4+2') from dual

It will return 6.

Generally speaking - using dynamic SQL you can easily achieve this.

edited Mar 21 '19 at 17:12

Ikhlak S.

8,578
10
57
77

answered Mar 21 '19 at 17:08

Saad Ahmad

393
1
7

It's MySQL Version 5.6 – Mike Mar 21 '19 at 17:10
I dont think MySQL allows dynamic SQL inside functions. So simple route of simply letting the SQL engine do it for you will not be available. Based on your needs if you can live with a stored procedure that can certainly call dynamic SQL. Or you can create a function that acytually parses string on +/*- etc. and has a switch statement etc. to compute the result. Not very complex. – Saad Ahmad Mar 21 '19 at 17:57

Convert a string with operation to int using sql query

3 Answers3