SQL query to return string from int columns

[cridley]

Hello, a quick question.
(MySQL)

I have an INTEGER col in a table and am doing something like
SELECT `col` FROM `table`
which returns, say,

1,1,1,2,2,1,3,2 or whatever.

Is there a way i can tell MySQL, in a query, to return strings in place of the numbers? ie, IF 1 THEN 'one', IF 2 THEN 'two', etc...
I might have to do this a fair bit and it would be better than having to put a load of display code in my app..
I have a feeling it is possible but I'm still at a relatively basic level in SQL.
Any suggestions?
Thx.

[sridhar_423]

write a function which will define the Strings for the numbers .. i.e. i-->"one", 2-->"two" and so on. Doing all this in the query by using "Case" will make it look very clumsy.

[cridley]

sorry, got it already...

SELECT CASE 1 WHEN col=1 THEN 'one' WHEN col=2 THEN 'two' ELSE 'other' END

onad so on...

[cridley]

yes, will also try a function, was just rying to figure the actual syntax, Thanks

[sridhar_423]

wrong syntax...

SELECT CASE 1 WHEN col=1 THEN 'one' WHEN col=2 THEN 'two' ELSE 'other' END

onad so on...

SELECT CASE WHEN col=1 THEN 'one' ELSE CASE WHEN col=2 THEN 'two' ELSE 'other' END END from TABLENAME where ....

[cridley]

I dunno, the one I posted works as well...

http://dev.mysql.com/doc/refman/5.0/en/case-statement.html (for stored routines)

From mysql 5.0 reference manual (I should'a looked there BEFORE posting) :rolleyes: :

mysql> SELECT CASE 1 WHEN 1 THEN 'one'
-> WHEN 2 THEN 'two' ELSE 'more' END;
-> 'one'
mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
-> 'true'
mysql> SELECT CASE BINARY 'B'
-> WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
-> NULL


You're in fact nesting a whole bunch of cases?
Thx anyways.

[sridhar_423]

SQL(Oracle) doesn't support the syntax that you've wrote.

[cridley]

Maybe not, but as I said, I'm only interested in mySQL for the time being. Have also written that function btw, all works well,
cheers.

[mattyblah]

Why not write the query like this:

SELECT CASE col WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'other' END

[cridley]

indeed, realised that after looking at the example i posted. a function is much neater though as you just have SELECT MyFunction(col). Wether this affects performance in any way i dunno though.

[NogDog]

SELECT ELT(col, 'one', 'two', 'three', 'four', 'five', 'six', 'seven', 'eight', 'nine') ...

[cridley]

great scott! I even looked at that function in the manual and never even realized that was exactly what i was after... still i just learnt how to store functions in mySQL as a result, might come in handy.
cheers nog, top dog.