STR() function

In Sql Server STR() function converts a numeric value to character data. STR() function allows us to specify the total length and number of decimal places to be included when it is converted to character data.

Summery

you can use STR() function as:

STR ( _expression [ , _length [ , _decimalPlaces ] ] )

where
_expression is a numeric data type value with a decimal point.
_length is the total length. The default is 10.
_decimalPlaces is the number of places to the right of the decimal point. decimal must be less than or equal to 16.  

Example

See the following queries:

SELECT str(123.5678,6,1) 
SELECT str(123.5678,7,2) 
SELECT str(123.5678,8,3)

output will be :

—————–
123.6

—————–
123.57

—————–
123.568

What needs to be noted while use STR() function

The specified length should be greater than or equal to the part of the number before the decimal point. In the following example the numneric expression 1237.5678 has length 4 before the decimal point, and the specified length is 3, in this case the specified length is less then the part of the number before the decimal point and the string returns *** for the specified length.

select str(1237.5678,3,2)

—————–
***

CHAR() function

 
Converts an int ASCII code to a character.

Summery

you can use STR() function as:

CHAR ( integer_exp )

where
integer_exp is an integer from 0 through 255.
 

Example

Suppose you have an table tbl_employee:
 
tbl_employee

The following example uses CHAR(36) to print the salary with dollar sign when the results are returned in text.

SELECT CHAR(36) + str(salary,8,2) AS Salary FROM tbl_employee

Salary
—————–
$ 2500.00
$ 3000.00

It is also useful to insert control character to your output, char(9) returns TAB, char(10) returns a line feed and char(13) returns a carriage.

ASCII() function

 
Returns the ASCII code value of the leftmost character of a string.

Summery

you can use ASCII() function as:

ASCII ( character_exp )

where character_exp is a char or varchar value.
 

Example

SELECT ascii('$')

Output will be:

—————–
36