CHAR String Function¶

The CHAR string function converts an integer ASCII code to a character. The syntax of the CHAR string function is as follows:

CHAR ( < integer_expression > )

The < integer_expression > is an integer value from 0 through 255. A NULL value is returned if the integer expression is not in this range.

Sample Uses of the CHAR Function¶

Here are sample uses of the CHAR string function

Usage #1 : Looking for Non-printable Characters within a String¶

SELECT *
FROM [dbo].[Products]
WHERE [Description] LIKE '%[' + CHAR(9) + CHAR(10) + CHAR(13) + ']%'

Usage #2 : Generating a Tab-Delimited File¶

SELECT CAST([CompanyID] AS VARCHAR(10)) + CHAR(9) + [CompanyName] + CHAR(9) + [Address] + CHAR(9) +
       [City] + CHAR(9) + [State] + CHAR(9) + [ZIP] + CHAR(13)
FROM [dbo].[Company]

Usage #3 : Generate a Random Password¶

DECLARE @NewPassword                  VARCHAR(20)
DECLARE @PasswordLength               INT
DECLARE @Index                        INT

SET @NewPassword = ''
SET @Index = 1
SET @PasswordLength = 10
WHILE @Index <= @PasswordLength
BEGIN
    SET @NewPassword = @NewPassword + CHAR(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 93 + 33)
    SET @Index = @Index + 1
END

SELECT @NewPassword

Usage #4 : Create an Address Label¶

SELECT [CompanyName] + CHAR(13) + [Address] + CHAR(13) + 
       [City] + ', ' + [State] + ' ' + [ZIP] + CHAR(13) + CHAR(13)
FROM [dbo].[Company]

Usage #5 : Getting a Currency Symbol¶

SELECT CHAR(36) AS [Dollar Sign], CHAR(162) AS [Cent Sign], CHAR(163) AS [Pound Sign],
       CHAR(164) AS [Currency Sign], CHAR(165) AS [Yen Sign]

Dollar Sign Cent Sign Pound Sign Currency Sign Yen Sign
----------- --------- ---------- ------------- --------
$           ¢         £          ¤             ¥