Escaping international (unicode) characters in string

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Greg R. Broderick - 29 May 2007 14:22 GMT

Y'all:

I am needing some way, in the SQL Server dialect of SQL, to escape unicode
code points that are embedded within an nvarchar string in a SQL script,
e.g. in Java I can do:

String str = "This is a\u1245 test.";

in Oracle's SQL dialect, it appears that I can accomplish the same thing:

INSERT INTO TEST_TABLE (TEST_COLUMN) VALUES ('This is a\1245 test.");

I've googled and researched through the MSDN, and haven't discovered a
similar construct in SQL Server. I am already aware of the UNISTR()
function, and the NCHAR() function, but those aren't going to work well if
there are more than a few international characters embedded within a
string.

Does anyone have a better suggestion?

Thanks muchly!
GRB

Signature

---------------------------------------------------------------------
Greg R. Broderick usenet200705@blackholio.dyndns.org

A. Top posters.
Q. What is the most annoying thing on Usenet?
---------------------------------------------------------------------

Reply to this Message

Erland Sommarskog - 29 May 2007 14:39 GMT

> I am needing some way, in the SQL Server dialect of SQL, to escape unicode
> code points that are embedded within an nvarchar string in a SQL script,
> e.g. in Java I can do:
>
> String str = "This is a\u1245 test.";

SELECT @str = 'This is a' + nchar(1245) + ' test'

Note here that 1245 is decimal. If you want to use hex code (which you
normally do with Unicode), you would do:

SELECT @str = 'This is a' + nchar(0x1245) + ' test'

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

Greg R. Broderick - 29 May 2007 18:01 GMT

>> I am needing some way, in the SQL Server dialect of SQL, to escape
>> unicode code points that are embedded within an nvarchar string in a
[quoted text clipped - 8 lines]
>
> SELECT @str = 'This is a' + nchar(0x1245) + ' test'

When there are more than one or two non-US-ASCII characters in the string,
this quickly becomes impractically unwieldy, thus my comment in my original
posting:

--- quote ---

I am already aware of the UNISTR() function, and the NCHAR() function, but
those aren't going to work well if there are more than a few international
characters embedded within a string.

--- quote ---

Thanks anyway, though. :-)

Signature

Reply to this Message

Erland Sommarskog - 29 May 2007 22:23 GMT

> When there are more than one or two non-US-ASCII characters in the
> string, this quickly becomes impractically unwieldy, thus my comment in
> my original posting:

If the are in sequence, you could do:

convert(nvarchar, 0x34123512...)

although this is certainly not too funny as you have twitch the bytes
around.

Another solution to use something like Microsoft Visual Keyboard, and
simply put the actual characters there.

Signature

Reply to this Message