Unicode Database

[TNT79]

hey gang!!

How can i change the DATA in the database into UNICODE ...


regards,

TNT

[M Owen]

Which database are you talking about?

[TNT79]

Thanx for replying M Owen,

i am using SQLSERVER 2000 and MS Access 2002

Thanx again,
TNT.

[antares686]

NVARCHAR, NCHAR, NTEXT and so on are the datatypes in SQL 2000 that support UNICODE data into them. Keep in mind they are half as large as there non-unicode counterparts as they take up 2 bytes per character.

[TNT79]

thanx for replying antares686,

The database schema uses nvarchar nchar and ntext ..
its the actual DATA that is not in unicode...

i need a way of converting the existing data into unicode ...

any ideas

many thanx
TNT

[antares686]

The data is already UNICODE even if it is varchar.

For example the letter A is A in unicode. But if you used a character with an value greater than 255 this is where UNICODE comes in (such as foreing language or other special characters not represented in the standard 255 set). You OS has to support it as well to view and input unicode characters.

So unless I don't see something here there is nothing you need to do. So what are you wanting to accomplish, maybe you are on the wrong path.

[TNT79]

hi there,

we have Greek\German\English data in the database that is not unicode.

i just need a way of transforming ALL existing data into unicode, prefrebley using SQL or TSQL...

thanx for your help !!!

TNT

[TNT79]

hey plp;

i managed to solved my problem with very simple SQL.
Below is the solution for anyone else who has the same problem...

[1] ...

// this function returns a unicode character having got a GREEK_ASCII character

FUNCTION [dbo].[getUnicodeChar] (@GreekChar CHAR )
RETURNS NCHAR AS
BEGIN
DECLARE @UniChar NCHAR
SET @UniChar =
CASE UNICODE(@GreekChar)
WHEN 0x80 THEN NCHAR(0x20AC)
WHEN 0x82 THEN NCHAR(0x201A)
WHEN 0x83 THEN NCHAR(0x0192)
WHEN 0x84 THEN NCHAR(0x201E)
WHEN 0x85 THEN NCHAR(0x2026)
WHEN 0x86 THEN NCHAR(0x2020)
WHEN 0x87 THEN NCHAR(0x2021)
WHEN 0x89 THEN NCHAR(0x2030)
WHEN 0x8B THEN NCHAR(0x2039)
WHEN 0x91 THEN NCHAR(0x2018)
WHEN 0x92 THEN NCHAR(0x2019)
WHEN 0x93 THEN NCHAR(0x201C)
WHEN 0x94 THEN NCHAR(0x201D)
WHEN 0x95 THEN NCHAR(0x2022)
WHEN 0x96 THEN NCHAR(0x2013)
WHEN 0x97 THEN NCHAR(0x2014)
WHEN 0x99 THEN NCHAR(0x2122)
WHEN 0x9B THEN NCHAR(0x203A)
WHEN 0xA0 THEN NCHAR(0x00A0)
WHEN 0xA1 THEN NCHAR(0x0385)
WHEN 0xA2 THEN NCHAR(0x0386)
WHEN 0xA3 THEN NCHAR(0x00A3)
WHEN 0xA4 THEN NCHAR(0x00A4)
WHEN 0xA5 THEN NCHAR(0x00A5)
WHEN 0xA6 THEN NCHAR(0x00A6)
WHEN 0xA7 THEN NCHAR(0x00A7)
WHEN 0xA8 THEN NCHAR(0x00A8)
WHEN 0xA9 THEN NCHAR(0x00A9)
WHEN 0xAB THEN NCHAR(0x00AB)
WHEN 0xAC THEN NCHAR(0x00AC)
WHEN 0xAD THEN NCHAR(0x00AD)
WHEN 0xAE THEN NCHAR(0x00AE)
WHEN 0xAF THEN NCHAR(0x2015)
WHEN 0xB0 THEN NCHAR(0x00B0)
WHEN 0xB1 THEN NCHAR(0x00B1)
WHEN 0xB2 THEN NCHAR(0x00B2)
WHEN 0xB3 THEN NCHAR(0x00B3)
WHEN 0xB4 THEN NCHAR(0x0384)
WHEN 0xB5 THEN NCHAR(0x00B5)
WHEN 0xB6 THEN NCHAR(0x00B6)
WHEN 0xB7 THEN NCHAR(0x00B7)
WHEN 0xB8 THEN NCHAR(0x0388)
WHEN 0xB9 THEN NCHAR(0x0389)
WHEN 0xBA THEN NCHAR(0x038A)
WHEN 0xBB THEN NCHAR(0x00BB)
WHEN 0xBC THEN NCHAR(0x038C)
WHEN 0xBD THEN NCHAR(0x00BD)
WHEN 0xBE THEN NCHAR(0x038E)
WHEN 0xBF THEN NCHAR(0x038F)
WHEN 0xC0 THEN NCHAR(0x0390)
WHEN 0xC1 THEN NCHAR(0x0391)
WHEN 0xC2 THEN NCHAR(0x0392)
WHEN 0xC3 THEN NCHAR(0x0393)
WHEN 0xC4 THEN NCHAR(0x0394)
WHEN 0xC5 THEN NCHAR(0x0395)
WHEN 0xC6 THEN NCHAR(0x0396)
WHEN 0xC7 THEN NCHAR(0x0397)
WHEN 0xC8 THEN NCHAR(0x0398)
WHEN 0xC9 THEN NCHAR(0x0399)
WHEN 0xCA THEN NCHAR(0x039A)
WHEN 0xCB THEN NCHAR(0x039B)
WHEN 0xCC THEN NCHAR(0x039C)
WHEN 0xCD THEN NCHAR(0x039D)
WHEN 0xCE THEN NCHAR(0x039E)
WHEN 0xCF THEN NCHAR(0x039F)
WHEN 0xD0 THEN NCHAR(0x03A0)
WHEN 0xD1 THEN NCHAR(0x03A1)
WHEN 0xD3 THEN NCHAR(0x03A3)
WHEN 0xD4 THEN NCHAR(0x03A4)
WHEN 0xD5 THEN NCHAR(0x03A5)
WHEN 0xD6 THEN NCHAR(0x03A6)
WHEN 0xD7 THEN NCHAR(0x03A7)
WHEN 0xD8 THEN NCHAR(0x03A8)
WHEN 0xD9 THEN NCHAR(0x03A9)
WHEN 0xDA THEN NCHAR(0x03AA)
WHEN 0xDB THEN NCHAR(0x03AB)
WHEN 0xDC THEN NCHAR(0x03AC)
WHEN 0xDD THEN NCHAR(0x03AD)
WHEN 0xDE THEN NCHAR(0x03AE)
WHEN 0xDF THEN NCHAR(0x03AF)
WHEN 0xE0 THEN NCHAR(0x03B0)
WHEN 0xE1 THEN NCHAR(0x03B1)
WHEN 0xE2 THEN NCHAR(0x03B2)
WHEN 0xE3 THEN NCHAR(0x03B3)
WHEN 0xE4 THEN NCHAR(0x03B4)
WHEN 0xE5 THEN NCHAR(0x03B5)
WHEN 0xE6 THEN NCHAR(0x03B6)
WHEN 0xE7 THEN NCHAR(0x03B7)
WHEN 0xE8 THEN NCHAR(0x03B8)
WHEN 0xE9 THEN NCHAR(0x03B9)
WHEN 0xEA THEN NCHAR(0x03BA)
WHEN 0xEB THEN NCHAR(0x03BB)
WHEN 0xEC THEN NCHAR(0x03BC)
WHEN 0xED THEN NCHAR(0x03BD)
WHEN 0xEE THEN NCHAR(0x03BE)
WHEN 0xEF THEN NCHAR(0x03BF)
WHEN 0xF0 THEN NCHAR(0x03C0)
WHEN 0xF1 THEN NCHAR(0x03C1)
WHEN 0xF2 THEN NCHAR(0x03C2)
WHEN 0xF3 THEN NCHAR(0x03C3)
WHEN 0xF4 THEN NCHAR(0x03C4)
WHEN 0xF5 THEN NCHAR(0x03C5)
WHEN 0xF6 THEN NCHAR(0x03C6)
WHEN 0xF7 THEN NCHAR(0x03C7)
WHEN 0xF8 THEN NCHAR(0x03C8)
WHEN 0xF9 THEN NCHAR(0x03C9)
WHEN 0xFA THEN NCHAR(0x03CA)
WHEN 0xFB THEN NCHAR(0x03CB)
WHEN 0xFC THEN NCHAR(0x03CC)
WHEN 0xFD THEN NCHAR(0x03CD)
WHEN 0xFE THEN NCHAR(0x03CE)

ELSE @GreekChar
END
RETURN @UniChar
END



[2] ...

// Next, this function loops through a greek varchar passing each character to the getUnicodeChar function (above)

FUNCTION [dbo].[ConvertToUnicode] (@strField VARCHAR (4000))
RETURNS NVARCHAR(4000) AS
BEGIN

DECLARE @intLoop Integer
DECLARE @intLength Integer
DECLARE @strRetVal nvarchar(4000)
DECLARE @uniChar NCHAR
DECLARE @greekChar CHAR

SET @intLength = Len(@strField)
SET @intloop = 0
SET @strRetVal = ''
SET @uniChar = ''
SET @greekChar = ''

WHILE (@intLoop < @intLength)
BEGIN
SET @intLoop = @intLoop + 1
SET @greekChar = SUBSTRING(@strField,@intloop,1)
EXEC @uniChar = getUnicodeChar @greekChar
SET @strRetVal = @strRetVal + @uniChar
END


RETURN @strRetVal



[3] ...
// Example (calls ConvertToUnicode )

DECLARE @str VARCHAR(4000)
DECLARE @Str2 NVARCHAR(4000)
set @str = CHAR(212)+CHAR(230)+CHAR(234)+CHAR(174)
EXEC @Str2 = ConvertToUnicode @str
print @Str2



All i have to do now is run it through the database

i hope this helps some out there !!!

P.S. this will only work for GREEK -> UNICODE ... if you want to change from other languages, you'll have to change getUnicodeChar function to accomodate this ...

regards,
TNT;