Review
5. Numeric functions: STR()
So you think you can count?
Rounding functions and more
Review and practice

Instruction

Okay. We can obtain a string by concatenating strings with numeric values. We can also turn decimal values into string values using the STR() function.

When you use STR() with just a number parameter, it will truncate everything after the decimal point. Here we have:

SELECT STR(123.56);

This returns a string with the value of '124'.

STR() has two other parameters: length and decimal digits. The length includes all numbers, signs, spaces and decimal points in the value we want to obtain. The decimal digits argument is the number of digits in the numeric value. Look at the example:

SELECT STR(123.56, 5, 1);

The result is '123.6'. The number is rounded to one decimal place, and any remaining decimal digits are cut off. However, for the following:

SELECT STR(123.56, 4, 1);

we get '124', because we've set the length to 4. This means we take only the first 4 chars from the value (123.) and round them up using the remaining digits. In this case, since a 5 followed the decimal point, we round up to 124.

Exercise

In the Character table, each character's weight is presented as a decimal number. The maximum length of the Weight in our table is 5. Retrieve each character's Id and Weight as a string. Show weight as a number with only one digit after the decimal point. Name the column Weight.

Stuck? Here's a hint!

You will need STR(Weight, 5, 1).