how to change the data type in mysql query?

Question

I have two tables question and question_paper . in question_paper i have a column named question_ids .in question_ids-(text type) i stored the question table's id like 2,5,23,45,6,9 etc. now the problem is when i trying to fetch the data using

select * from question where id IN (select question_ids from question where id=1) it passes the question_ids as a string type so it returns only first matched id's value.so, i need to change this query result as a number type is it possible in mysql or is there any function using i can change the type temporary.thanks in advanced.

Have you tried CAST() or CONVERT()? Just google "mysql cast" — yahermann, Jan 03 '16 at 17:43
Try this [SO post](http://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows) to first split your question_id string into rows and then use that for your sub-query. I think that should work. — vmachan, Jan 03 '16 at 17:45

score -1 · Answer 1 · answered Jan 03 '16 at 17:47

-1

MySQL CAST() function to convert a value of any type into a value with a specified type. http://www.mysqltutorial.org/mysql-cast/

answered Jan 03 '16 at 17:47

Vipin Jain

3,686
16
35

I tried but not working i am confused with subquery how to use CAST() with subquery . is this right way "select * from question where id IN (CAST((select question_ids from question where id=1) AS UNSIGNED ))" – Alfiza malek Jan 03 '16 at 17:56
is there any another way like i can remove the '' quotes from the result of subquery. – Alfiza malek Jan 03 '16 at 18:00
1

in your question_id have commas so how it is possible to cast in UNSIGNED? – Vipin Jain Jan 03 '16 at 18:00
yeah agree but i need something that can change the subquery result from string to number. – Alfiza malek Jan 03 '16 at 18:07
You should split question_ids and use in the subquery – Vipin Jain Jan 03 '16 at 18:07
ya i did this for temporary if i got the solution i ll update.Thanks to all of you. – Alfiza malek Jan 03 '16 at 18:10

how to change the data type in mysql query?

1 Answers1