sql select replace integer with string

Question

I’m pulling data form a MySQL database where integer values correlate to more meaningful string outputs. For example, a relation’s attribute “Sport” is defined as integer that can have the value of 1-4. Where 1 => Basketball, 2= Football, 3 = Soccer, 4 = Kickball. Or even a more trivial example, “Employed” where 1=> True, 0=> False. When I pull these integer values out to display them to a user, I need to display the related string value. A co-worker mentioned that he thought there was a MySQL function that would substitute this integer value for the corresponding string. I looked briefly at REPLACE, but do not want to change the value of the stored attribute; just the value of attribute during that SELECT. Is there a SQL function to accomplish this?

My solution to the problem was to use PHP to check the integer value of the returned attribute and replace it accordingly. But, I couldn’t help but wonder what was the best practice for this situation? Is there a distinct computational advantage or disadvantage to either? I assume that if there was swap functionality to SQL query that it would save time versus having to do a conditional check each time I encounter the variable in PHP.

This also led me to ponder if the database should just hold string values? What is the best practice here?

Answer 1

The CASE expression could help. However, it may be even faster to have a small table with an int primary key and a name string such as

1  baseball
2  football

etc, and JOIN it appropriately in the query.

Answer 2

The database should hold the values and you should perform a join to another table which has that data in it.

So you should have a table which has say a list of people

ID Name FavSport
1 Alex 4
2 Gnats 2

And then another table which has a list of the sports

ID Sport
1 Basketball
2 Football
3 Soccer
4 Kickball

Then you would do a join between these tables

select people.name, sports.sport from people, sports where people.favsport = sports.ID

which would give you back

Name Sport
Alex Kickball
Gnat Football

You could also use a case statement eg. just using the people table from above you could write something like

select name, case when favsport = 1 then 'Basketball' when favsport = 2 then 'Football' when favsport = 3 then 'Soccer' else 'Kickball' end as "Sport" from people

But that is certainly not best practice.

Answer 3

Does MySQL have a CASE statement. I am not logged into my server right now, so I can't doublecheck, but something like the following works in SQL Server:

SELECT
    CASE MyColumnName
     WHEN 1 THEN 'First'
     WHEN 2 THEN 'Second'
     WHEN 3 THEN 'Third'
     ELSE 'Other'
    END

Answer 4

Do you think it would be helpful to store these relationships between integers and strings in the database itself? As long as you have to store these relationships, it makes sense to store it close to your data (in the database) instead of in your code where it can get lost. If you use this solution, this would make the integer a foreign key to values in another table. You store integers in another table, say sports, with sport_id and sport, and join them as part of your query.

Instead of SELECT * FROM my_table you would SELECT * from my_table and use the appropriate join. If not every row in your main column has a corresponding sport, you could use a left join, otherwise selecting from both tables and using = in the where clause is probably sufficient.

Answer 5

definitely have the DB hold the string values. I am not a DB expert by any means, but I would recommend that you create a table that holds the strings and their corresponding integer values. From there, you can define a relationship between the two tables and then do a JOIN in the select to pull the string version of the integer.

tblSport Columns
------------
SportID int (PK, eg. 12)
SportName varchar (eg. "Tennis")


tblFriend Columns
------------
FriendID int (PK)
FriendName (eg. "Joe")
LikesSportID (eg. 12)


In this example, you can get the following result from the query below:
SELECT FriendName, SportName
FROM tblFriend
INNER JOIN tblSport
ON tblFriend.LikesSportID = tblSport.SportID

Man, it's late - I hope I got that right. by the way, you should read up on the different types of Joins - this is the simplest example of one.