Convert string to ASCII

A simple SQL function I created to convert a string to ASCII values.

-- =============================================
-- Author:		Amit Singh
-- Description:	Convert string to ASCII values
-- Usage: 		SELECT dbo.ToASCII('Amit')
-- =============================================
CREATE FUNCTION ToASCII
(@string AS VARCHAR(2000))
RETURNS VARCHAR(5000)
AS
BEGIN
	SET @string = UPPER(@string)

	DECLARE @Output VARCHAR(5000)
	DECLARE @position INT
	SET @position = 1

	WHILE @position <= DATALENGTH(@string)
	BEGIN
		SELECT @Output = COALESCE(@Output, '')
					+ CAST(ASCII(SUBSTRING(@string, @position, 1)) AS VARCHAR(3))
		SET @position = @position + 1
	END

	RETURN @Output
END
-- =============================================
GO

This function is build as per my requirements but you can modify it to get desired results.
For example you can replace

COALESCE(@Output, '')

with

COALESCE(@Output + ',', '')

to get a delimited output.

Advertisements

About Amit Singh

Software engineer; crazy about music, astronomy and radio controlled aircrafts. Big science/technology buff!
This entry was posted in Programming, SQL and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s