Use Stars in SQL Server

Create a histograph in SQL Server: "Create a histograph in SQL Server

by Arthur Fuller | More from Arthur Fuller | Published: 8/15/05

Takeaway:
Arthur Fuller worked on a project recently that assigned star ratings to each problem. This system led him to create code that demonstrates how you would present this data via SQL Server. Check out his non database-dependent example code.

Star ratings are often used in film, hotel, and restaurant ratings. However, I worked on a project recently that used the star ratings scale to identify how critical a problem is; for instance, the more stars, the more urgent it was that the problem be addressed. This led me to wonder how you would present this data via SQL Server.

For starters, you can store the number of stars as a smallint, or even derive it using a calculation, as the statement below illustrates. Then comes the issue of presentation—how to turn a rating of 5 into five asterisks, as they might appear in a newspaper's film or restaurant review.

Now you must decide how you want to deliver the data (which in this case are star ratings). You have two choices:

You can leave it up to the client. This is easy for you, but hard for the developers of each client because the more clients, the more redundant code.
You can do it in the database. There's no downside to this option, and every client can simply extract the column without having to calculate.
In a case like this, I choose to deliver the result from the back-end and not the front-end. My reasoning is simple: For every additional front-end, new application-specific code is required. When you deliver the result from the back-end, new applications can reuse code.

A non database-dependent example
Let's suppose a table contains a column called NumberOfStars (varchar(5)), as well as all of the other required details. We can create a virtual column of type varchar that presents as many stars as the NumberOfStars column wants.

To begin, use this code (you may substitute any valid column reference for the second parameter):

SELECT REPLICATE( '*', 4)Assuming a column called Stars is in a table called Reviews, you could write the following:

SELECT REPLICATE( '*', Reviews.Stars)
FROM ReviewsTry this command against the Northwind database:

SELECT Quantity, REPLICATE( '*', Quantity/5)
FROM [Order Details]
WHERE Discount IS NOT NULLThis will result in a list like this (the list is truncated to save space):

12 **
10 **
5 *
9 *
40 ********
10 **
35 *******
15 ***
6 *
15 ***In the code above, I use the most generic alphabet. Depending on the fonts available, you could make it more interesting by substituting a graphic character for the asterisk. For example, take a look through the three Wingdings fonts. If you see something you like, substitute that character for the asterisk. You won't see the results in Query Analyzer, which is typographically agnostic, but you should see them in another client.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!"