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
– user1666620, Commented Sep 25, 2015 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
– RBarryYoung, Commented Sep 25, 2015 at 15:52
Typically you'd have a lookup table that maps the int value of the enum to the corresponding name for use in SQL. — juharr
– juharr, Commented Sep 25, 2015 at 16:09

juharr · Accepted Answer · 2015-09-25 17:04:09Z

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, 2015 at 17:04

answered Sep 25, 2015 at 16:25

juharr

32.4k4 gold badges60 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

mustang00 Over a year ago

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.

JonH Over a year ago

This is pretty basic - but yes you would need to have another table. Notice the join he has used.

juharr Over a year ago

It depends on how you deploy your DB. The table should be created and populated as part of that process.

mustang00 Over a year ago

@jurharr Tried this, but doesn't return any records. But there are records for dispatched.

juharr Over a year ago

Did you populate the StatusLookup table with values?

user1666620 · Accepted Answer · 2015-09-25 15:55:34Z

2

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.

edited Sep 25, 2015 at 15:55

answered Sep 25, 2015 at 15:50

user1666620

4,81821 silver badges28 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 10:26:59Z

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, 2017 at 10:26

CommunityBot

11 silver badge

answered Sep 25, 2015 at 15:58

oleksii

36.1k16 gold badges98 silver badges173 bronze badges

7 Comments

mustang00 Over a year ago

Hi @oleksii, thanks for your response. I want to achieve this in the SQL query, is it possible to do so?

oleksii Over a year ago

@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.

mustang00 Over a year ago

I have done so, yet it still displays numbers instead of the actual text value in place of that number.

oleksii Over a year ago

where do you run it? What is it?

mustang00 Over a year ago

I run the query in the query builder in VS2013. So when I run the query it only displays the number value of theStatus not the name 'Dispatched'

|

Collectives™ on Stack Overflow

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

3 Answers 3

5 Comments

Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related