SQL select replace integer with string

Question

Goal is to replace a integer value that is returned in a SQL query with the char value that the number represents. For example:

A table attribute labeled ‘Sport’ is defined as a integer value between 1-4. 1 = Basketball, 2 = Hockey, etc. Below is the database table and then the desired output.

Database Table:

Player     Team     Sport
--------------------------
Bob        Blue     1
Roy        Red      3
Sarah      Pink     4 

Desired Outputs:

Player     Team     Sport
------------------------------
Bob        Blue     Basketball
Roy        Red      Soccer
Sarah      Pink     Kickball

What is best practice to translate these integer values for String values? Use SQL to translate the values prior to passing to program? Use scripting language to change the value within the program? Change database design?

Answer 1

MySQL has a CASE statement. 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 2

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.

Answer 3

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 4

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 5

In oracle you can use the DECODE function which would provide a solution where the design of the database is beyond your control.

Directly from the oracle documentation:

Example: This example decodes the value warehouse_id. If warehouse_id is 1, then the function returns 'Southlake'; if warehouse_id is 2, then it returns 'San Francisco'; and so forth. If warehouse_id is not 1, 2, 3, or 4, then the function returns 'Non domestic'.

SELECT product_id,
       DECODE (warehouse_id, 1, 'Southlake', 
                             2, 'San Francisco', 
                             3, 'New Jersey', 
                             4, 'Seattle',
                                'Non domestic') "Location" 
  FROM inventories
  WHERE product_id < 1775
  ORDER BY product_id, "Location";

Answer 6

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.