How to display the string Enum values instead of the number value using SQL

Question

I'm trying to display a list of all Deliveries with the status Dispatched. However, its only returning the number value of the status as opposed to the actual string value. I think this is because I have used Enum to store my status values?

I wish to display the word Dispatched instead of the number value that it represents in the Enum.

I'm developing in ASP.Net MVC and I'm using the query builder in VS2013.

I'm not sure how to approach this, can anyone please suggest an easy to understand solution using SQL.

Let me know if any additional code is required, and thank you in advance!

Here's the Query I want but it doesn't work:

SELECT Delivery.[Status], 
COUNT(Delivery.DeliveryID) AS Dispatched_Status 
FROM Delivery 
WHERE Delivery.[Status] = 'Dispatched'
GROUP BY Delivery.[Status];

Here's the Query that does work but returns a number value. I tried it this way because Enum stores the string value as a number:

SELECT Delivery.[Status], 
COUNT(Delivery.DeliveryID) AS Dispatched_Status 
FROM Delivery 
WHERE Delivery.[Status] = '1'
GROUP BY Delivery.[Status];

P.S I'm aware that status is a reserved word - will be following the correct naming conventions in future.

Delivery Table Definion

store the enum text in another field on the record. Also, is this for MySQL or SQL Server, because you have both tags. — user1666620, Sep 25 '15 at 15:43
Show us the [Delivery] table definition. It's in there. If you don't know how to do it, try doing `SELECT * FROM Delivery` instead to see what the other column names are. — RBarryYoung, Sep 25 '15 at 15:52
@RBarryYoung I think the issue is that `Enum` stores the `Delivery_Status` as an `int` — mustang00, Sep 25 '15 at 15:54
@mustang00 Perhaps, but that would be unusual. If he follows my instructions, it will answer the question either way. — RBarryYoung, Sep 25 '15 at 15:56
Please show us the `Delivery` table definition. Is the `Status` column defined as an integer or a varchar (or other character data type)? In your example query you're doing the comparison `WHERE Delivery.[Status] = 'Dispatched'`, but you wrote that it returns a number value. — Adam Porad, Sep 25 '15 at 15:56
Apologies @AdamPorad, I want the query to look like above, but it only works if `WHERE Delivery.[Status] = '1'` — mustang00, Sep 25 '15 at 15:59
Are you using Entity Framework or some other type of ORM, or are you using ADO? — Boris B., Sep 25 '15 at 16:04
@mustang00 If so, then when are you in a situation where you: a) need raw SQL, and b) Need *output* of raw SQL? — Boris B., Sep 25 '15 at 16:07
@BorisB. b) I need to get the right output. It displays the output value in numbers whereas I want the output to be displayed in the string value represented by the number. — mustang00, Sep 25 '15 at 16:09
Typically you'd have a lookup table that maps the int value of the enum to the corresponding name for use in SQL. — juharr, Sep 25 '15 at 16:09
@juharr could you perhaps suggest an answer utilizing that method? — mustang00, Sep 25 '15 at 16:11
@mustang00 What I'm saying is that in all but advanced scenarios you'll *never* need the actual DB type or raw SQL output, so you don't have to care if internally it's represented as a number. If your model are EF objects then when you load the object it will have its proper value (of type Enum), you can then do with it what you like. You won't ever need the actual raw SQL output. — Boris B., Sep 25 '15 at 16:12
@BorisB. What if he's writing SQL in a reporting system? Then the OP needs a mapping in the DB. — juharr, Sep 25 '15 at 16:13
@mustang00 Check out this question http://stackoverflow.com/questions/19529959/map-table-column-to-enum-and-lookup-table-to-enum — juharr, Sep 25 '15 at 16:16

juharr · Accepted Answer · 2015-09-25T17:04:09.043

4

It sounds like you just need to add a lookup table in you DB. Something like

CREATE TABLE [dbo].[StatusLookup](
    [StatusID] [int] NOT NULL,
    [StatusName] [varchar](64) NOT NULL,
    [StatusDescription] [varchar](max),
)

INSERT INTO [dbo].[StatusLookup]([StatusID],[StatusName],[StatusDescription]
VALUES(1, 'Dispatched', 'A dispatched record')
...

Note you'll have to manually do this and make sure to populate it with values that match up with your enum.

Then your query would be

SELECT StatusLookup.[StatusName], 
COUNT(Delivery.DeliveryID) AS Dispatched_Status 
FROM Delivery 
JOIN StatusLookup ON Delivery.Status = StatusLookup.StatusID
WHERE StatusLookup.[StatusName] = 'Dispatched'
GROUP BY StatusLookup.[StatusName];

edited Sep 25 '15 at 17:04

answered Sep 25 '15 at 16:25

juharr

31,741
4
58
93

So I would need to add that table to my models or create it within the same query? Just to be clear so I can understand it better. – mustang00 Sep 25 '15 at 16:32
This is pretty basic - but yes you would need to have another table. Notice the join he has used. – JonH Sep 25 '15 at 16:34
It depends on how you deploy your DB. The table should be created and populated as part of that process. – juharr Sep 25 '15 at 16:35
@jurharr Tried this, but doesn't return any records. But there are records for dispatched. – mustang00 Sep 25 '15 at 16:50
Did you populate the StatusLookup table with values? – juharr Sep 25 '15 at 17:02

user1666620 · Answer 2 · 2015-09-25T15:55:34.153

Enums are stored as integers by default.

You can add a separate varchar or nvarchar field to your database table to hold the description of the enum, and populate it using something like the below:

string selectedEnumDescription = Enum.GetName(typeof(DeliveryStatusEnum), Delivery.Status)

The exact implementation depends on how you are saving your records, and what the actual properties and enum names are.

You can then just select the description column in your SQL query.

Either that or you could store the actual enum values and descriptions within a separate table and do a join.

score 1 · Answer 3 · edited May 23 '17 at 10:26

1

You can store enum in database as a number, usually a small number - the exact type depends on your database. When you read it - you convert a number to enum and work in your code with the enum. When you need to display it, you can call a ToString() method on that enum, for example

public enum Foo
{
    A,
    B
}

public class Program
{
    public static void Main()
    {
        Console.WriteLine(Foo.A.ToString()); // Prints    A
    }
}

See it working

You can also use description attribute and print that, see examples here and here

edited May 23 '17 at 10:26

Community

1
1

answered Sep 25 '15 at 15:58

oleksii

35,458
16
93
163

Hi @oleksii, thanks for your response. I want to achieve this in the `SQL query`, is it possible to do so? – mustang00 Sep 25 '15 at 16:03
@mustang00 sorry misunderstood you. In SQL query, you'd generally use a number, such as `... WHERE Delivery.[Status] = 1` - note there are no single quotes around the number 1. If you put them in, most likely db will think it's a single char. – oleksii Sep 25 '15 at 16:11
I have done so, yet it still displays numbers instead of the actual text value in place of that number. – mustang00 Sep 25 '15 at 16:12
where do you run it? What is *it*? – oleksii Sep 25 '15 at 16:16
I run the query in the query builder in VS2013. So when I run the `query` it only displays the number value of the`Status` not the name 'Dispatched' – mustang00 Sep 25 '15 at 16:18
Oh.... I think I now get you. So you run it in VS query builder and you would like your query builder to displace `Dispatched` instead of say `1`. There are several problems with that. **1** It's inefficient storage, it takes less space to store small number in db **2** If you rename/want to rename `Dispatched` to something like `Dispatching` - pain in the neck as you may have several versions in db after it has been used in production **3** You cannot support culture - if you want to add German or Japanese - you stuck with English in database. And there's probably more. – oleksii Sep 25 '15 at 16:22
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/90637/discussion-between-oleksii-and-mustang00). – oleksii Sep 25 '15 at 16:22

How to display the string Enum values instead of the number value using SQL

3 Answers3