String Function – CHAR() – Ramblings of a Crafty DBA

I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the CHAR() function today.

The CHAR() function takes an integer value, ASCII code, from 0 to 255 and returns a single character string.

The following example returns the character ‘0’ given the ASCII code of 48.

-- ASCII code for zero
declare @var_tmp int = 48;

-- Return the single character
select char(@var_tmp) as single_char;

-- ASCII code for zero

declare @var_tmp int = 48;

-- Return the single character

select char(@var_tmp) as single_char;

output:

single_char
-----------
0

output:

single_char

-----------

One thing to always worry about when using TSQL functions is how will it react with a UNKNOWN or NULL values?

-- UNKNOWN value returns NULL
select char(NULL) as single_char;

1 2	-- UNKNOWN value returns NULL select char(NULL) as single_char;

output:

single_char
-----------
NULL

output:

single_char

-----------

NULL

Another thing to test is how the function will work with values out of range?

-- Out of range values return NULL
select char(-1) as single_char;
select char(512) as single_char;

-- Out of range values return NULL

select char(-1) as single_char;

select char(512) as single_char;

output:

single_char
-----------
NULL

output:

single_char

-----------

NULL

Next time, I will be exploring the CHARINDEX() function.

String Function – CHAR()