asp / sql convert string to UCS2

I need to convert strings to UCS2 hex.

can anyone provide me with either an asp function, or sql stored proc / function that will accomplish this?

for example to convert "abc" to "006100620063"
Who is Participating?
Ephraim WangoyaConnect With a Mentor Commented:
Try this

select dbo.fnCharToUCSHex('abc')
CREATE FUNCTION fnCharToUCSHex(@input varchar(max)) 
RETURNS varchar(max)
	DECLARE @position int, @asciicode int, @ResultVar varchar(max), @hex varbinary(2)
	SET @ResultVar = ''
	SET @position = 1

	WHILE @position <= DATALENGTH(@input)
	   SET @asciicode = ASCII(SUBSTRING(@input, @position, 1))
	   SET @position = @position + 1
	   SET @hex = CONVERT(VARBINARY(2), @asciicode)
	   SET @ResultVar = @ResultVar + Right(master.dbo.fn_varbintohexstr(@hex), 4)

	RETURN @ResultVar

Open in new window

Dave BaldwinFixer of ProblemsCommented:
This page from the Unicode web site says that UCS2 is the old version and UTF-16 is the current version.  And on another page :

The older UCS-2 (2-byte Universal Character Set) is a similar character encoding that was superseded by UTF-16 in version 2.0 of the Unicode standard in July 1996.

At the moment, I can't find a routine that does the conversion for you.
One needs to be a bit careful here. The Windows API MultiByteToWideChar will convert a byte string into a "Unicode" string. UTF-16 means that each character is encoded in 16 bits. There are however TWO formats for Unicode strings. The one format is "006100620063"  the other "610062006300". The first format is called Big Endian format the second Little Endian. The Little Endian format is the usual one found on most platforms, particularly Microsoft. Big Endian occurs often with IBM and is less prevelant. File which contain Unicode strings have a Byte Order Marker (BOM) at the beginning. Microsoft files, ie Little Endian, have hex FF FE at the beginning. Big Endian would have FE FF.

The format which the questioner is asking for is then Big Endian. is this correct?
DanM711Author Commented:
Excellent, thank you so much, you really got me out of a hole there
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.