Solved

asp / sql convert string to UCS2

Posted on 2011-02-20
4
741 Views
Last Modified: 2012-06-27
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"
0
Comment
Question by:DanM711
4 Comments
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
Comment Utility
Try this

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

	WHILE @position <= DATALENGTH(@input)
	BEGIN
	   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)
	END

	RETURN @ResultVar
END

Open in new window

0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
This page from the Unicode web site http://www.unicode.org/faq/basic_q.html#14 says that UCS2 is the old version and UTF-16 is the current version.  And on another page http://en.wikipedia.org/wiki/UTF-16/UCS-2 :

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.
0
 
LVL 27

Expert Comment

by:BigRat
Comment Utility
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?
0
 

Author Closing Comment

by:DanM711
Comment Utility
Excellent, thank you so much, you really got me out of a hole there
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Granting full access permission allows users to access mailboxes present in their database. By giving full access permission one can open and read the content of any mailbox but cannot send emails from that mailbox.
Resolve Outlook connectivity issues after moving mailbox to new Exchange 2016 server
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now