XML FeedsNot all systems can correctly accommodate Unicode data. Therefore, it's not uncommon to receive data that is encoded in such a way that it can be stored in an ASCII format. Of course, SQL Server can store Unicode data easily if you use the nvarchar data type. Converting to and from Unicode data can be time consuming. When you receive data that is Unicode encoded, I suggest you decode it and store it in an nvarchar column. This will result in better performance.
Usually, when Unicode is encoded in an ASCII format, the Unicode value is somehow embedded within it. For example, the Greek letter Omega Ω, has a Unicode value of 937. It may be encoded like this: Ω
Usually, non Unicode data will not be encoded. So you may see a string like this: "Ωmega"
Converting from an encoded string to a real Unicode string is more difficult than it may seem. There are 65,535 possible Unicode characters (the first 255 match ASCII characters). The example I showed has 3 digit Unicode values, but we should also be able to accommodate 4 and 5 digit Unicode characters too.
The basis for the function below is the CharIndex SQL Server functions. CharIndex has an optional 3rd parameter that allows you to specify where (within the string) to start searching. What we do is first find where the encoded Unicode data starts, and then where it ends. Once we know this, we do a simple replace. Since there can be multiple encoded Unicode characters, we put the whole thing in to a while loop.
- Create Function DecodeUnicodeData(@Data nVarChar(4000), @Prefix VarChar(100), @Suffix VarChar(100))
- Returns nvarchar(4000)
- As
- Begin
- Declare @Start Int
- Declare @End Int
- While CharIndex(@Prefix, @Data) > 0
- Begin
- Set @Start = CharIndex(@Prefix, @Data)
- Set @End = CharIndex(@Suffix, @Data, @Start)
- Set @Data = Replace(@Data, SubString(@Data, @Start, @End -@Start + Len(@Suffix)),NCHAR(SubString(@Data, @Start+ Len(@Prefix),@End -@Start - Len(@Prefix))))
- End
- Return @Data
- End
You can call it like this:
- Select dbo.DecodeUnicodeData('Ωmega', '&#', ';')
This blog wouldn't be complete if I didn't also give you a function for encoding data, too. What I mean is, suppose you need to supply data in this format. How would you do it?
- Create Function EncodeUnicodeData(@Data NVarChar(4000), @Prefix VarChar(20), @Suffix VarChar(20))
- Returns VarChar(8000)
- As
- Begin
- Declare @i Int
- Declare @Output VarChar(8000)
- Set @i = 1
- Set @Output = ''
- While @i <= Len(@Data)
- Begin
- If Unicode(SubString(@Data, @i, 1)) > 255
- Set @Output = @Output + @Prefix + Convert(VarChar(5),Unicode(SubString(@Data, @i, 1))) + @Suffix
- Else
- Set @Output = @Output + SubString(@Data, @i, 1)
- Set @i = @i + 1
- End
- Return @Output
- End
You can call it like this:
- Select dbo.EncodeUnicodeData(N'Ωmega', '&#', ';')
Nice stuff George, now I can encode and decode that awesome Wingdings document I wrote :-)
Hi,
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.