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.
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
----------- --------- ---------- ------------- --------
$ ¢ £ ¤ ¥