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 a question on SO. — Paul Spiegel
– Paul Spiegel, Commented Mar 21, 2019 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
– Alex, Commented Mar 21, 2019 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
– Mike, Commented Mar 21, 2019 at 17:07
Possible duplicate of Summing a comma separated column in MySQL 4 (not 5) — Alex
– Alex, Commented Mar 21, 2019 at 17:12

Gordon Linoff · Accepted Answer · 2019-03-21 17:26:55Z

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, 2019 at 17:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Mike Over a year ago

Works! I knew there must be a way. Thanks!

Zaynul Abadin Tuhin · Accepted Answer · 2019-03-21 17:33:02Z

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, 2019 at 17:33

answered Mar 21, 2019 at 17:01

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

3 Comments

Mike Over a year ago

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.

Gordon Linoff Over a year ago

@Mike . . . This will also return 10 instead of 5 for '5'.

Zaynul Abadin Tuhin Over a year ago

@GordonLinoff i just updated answer for only sample data

Ikhlak S. · Accepted Answer · 2019-03-21 17:12:57Z

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, 2019 at 17:12

Ikhlak S.

9,10411 gold badges60 silver badges80 bronze badges

answered Mar 21, 2019 at 17:08

Saad Ahmad

4132 silver badges7 bronze badges

2 Comments

Mike Over a year ago

It's MySQL Version 5.6

Saad Ahmad Over a year ago

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.

Collectives™ on Stack Overflow

Convert a string with operation to int using sql query

3 Answers 3

1 Comment

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related