blocks|key|1929253|text|UTF-8不是一个字符集，它是一种编码。UTF-8的字符集是Unicode。如果要存储Unicode文本，则使用nvarchar数据类型。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1929254|如果数据库使用UTF-8存储文本，您仍然不会将文本作为编码的UTF-8数据提取出来，而是将其作为解码的文本提取出来。|1929255|您可以很容易地将UTF8编码的文本存储在数据库中，但是您不会将其存储为文本，而是将其存储为二进制数据(varbinary)。|1929256|entityMap^0|1K|8|0|0|1F|9|0^^$0|@$1|2|3|4|5|6|7|L|8|@$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|O|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|P|8|@$9|Q|A|R|B|C]]|D|@]|E|$]]|$1|J|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|K|$]]

UTF-8 is not a character set, it's an encoding. The character set for UTF-8 is Unicode. If you want to store Unicode text you use the <code>nvarchar</code> data type.

If the database would use UTF-8 to store text, you would still not get the text out as encoded UTF-8 data, you would get it out as decoded text.

You can easily store UTF-8 encoded text in the database, but then you don't store it as text, you store it as binary data (<code>varbinary</code>).

blocks|key|1929266|text|请注意，从Microsoft+SQL+Server+2016开始，bcp、BULK_INSERT和OPENROWSET都支持UTF-8。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1929267|附录2016-12-21:+SQL+Server+2016+SP1现在为所有版本的MS+SQL启用统一码压缩(以及其他大多数以前仅供企业使用的功能)，包括标准版和快捷版。这与UTF-8支持不同，但如果目标是减少西方字母表的磁盘空间，则会产生类似的好处。|1929268|entityMap|0|LINK|mutability|MUTABLE|url|https://msdn.microsoft.com/en-us/library/ms162802.aspx|1|https://msdn.microsoft.com/en-us/library/ms188365.aspx|2|https://msdn.microsoft.com/en-us/library/ms190312.aspx^0|X|3|11|B|1D|A|X|3|0|11|B|1|1D|A|2|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@$9|U|A|V|B|C]|$9|W|A|X|B|C]|$9|Y|A|Z|B|C]]|D|@$9|10|A|11|1|12]|$9|13|A|14|1|15]|$9|16|A|17|1|18]]|E|$]]|$1|F|3|G|5|6|7|19|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|1A|8|@]|D|@]|E|$]]]|I|$J|$5|K|L|M|E|$N|O]]|P|$5|K|L|M|E|$N|Q]]|R|$5|K|L|M|E|$N|S]]]]

Note that as of Microsoft SQL Server 2016, UTF-8 is supported by <a href="https://msdn.microsoft.com/en-us/library/ms162802.aspx" rel="nofollow noreferrer"><code>bcp</code></a>, <a href="https://msdn.microsoft.com/en-us/library/ms188365.aspx" rel="nofollow noreferrer"><code>BULK_INSERT</code></a>, and <a href="https://msdn.microsoft.com/en-us/library/ms190312.aspx" rel="nofollow noreferrer"><code>OPENROWSET</code></a>.

Addendum 2016-12-21: SQL Server 2016 SP1 now enables Unicode Compression (and most other previously Enterprise-only features) for all versions of MS SQL including Standard and Express. This is not the same as UTF-8 support, but it yields a similar benefit if the goal is disk space reduction for Western alphabets.

blocks|key|1715848|text|在T-SQL中处理UTF-8的两个UDF：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1715849|CREATE+Function+UcsToUtf8(@src+nvarchar(MAX))+returns+varchar(MAX)+as
begin
++++declare+@res+varchar(MAX)='',+@pi+char(8)='%25[%5E'%2Bchar(0)%2B'-'%2Bchar(127)%2B']%25',+@i+int,+@j+int
++++select+@i=patindex(@pi,@src+collate+Latin1_General_BIN)
++++while+@i>0
++++begin
++++++++select+@j=unicode(substring(@src,@i,1))
++++++++if+@j<0x800+++++select+@res=@res%2Bleft(@src,@i-1)%2Bchar((@j&1984)/64%2B192)%2Bchar((@j&63)%2B128)
++++++++else++++++++++++select+@res=@res%2Bleft(@src,@i-1)%2Bchar((@j&61440)/4096%2B224)%2Bchar((@j&4032)/64%2B128)%2Bchar((@j&63)%2B128)
++++++++select+@src=substring(@src,@i%2B1,datalength(@src)-1),+@i=patindex(@pi,@src+collate+Latin1_General_BIN)
++++end
++++select+@res=@res%2B@src
++++return+@res
end

CREATE+Function+Utf8ToUcs(@src+varchar(MAX))+returns+nvarchar(MAX)+as
begin
++++declare+@i+int,+@res+nvarchar(MAX)=@src,+@pi+varchar(18)
++++select+@pi='%25[à-ï][€-¿][€-¿]%25',@i=patindex(@pi,@src+collate+Latin1_General_BIN)
++++while+@i>0+select+@res=stuff(@res,@i,3,nchar(((ascii(substring(@src,@i,1))&31)*4096)%2B((ascii(substring(@src,@i%2B1,1))&63)*64)%2B(ascii(substring(@src,@i%2B2,1))&63))),+@src=stuff(@src,@i,3,'.'),+@i=patindex(@pi,@src+collate+Latin1_General_BIN)
++++select+@pi='%25[Â-ß][€-¿]%25',@i=patindex(@pi,@src+collate+Latin1_General_BIN)
++++while+@i>0+select+@res=stuff(@res,@i,2,nchar(((ascii(substring(@src,@i,1))&31)*64)%2B(ascii(substring(@src,@i%2B1,1))&63))),+@src=stuff(@src,@i,2,'.'),@i=patindex(@pi,@src+collate+Latin1_General_BIN)
++++return+@res
end|code-block|syntax|javascript|1715850|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Two UDF to deal with UTF-8 in T-SQL:

<pre><code>CREATE Function UcsToUtf8(@src nvarchar(MAX)) returns varchar(MAX) as
begin
 declare @res varchar(MAX)='', @pi char(8)='%[^'+char(0)+'-'+char(127)+']%', @i int, @j int
 select @i=patindex(@pi,@src collate Latin1_General_BIN)
 while @i&gt;0
 begin
 select @j=unicode(substring(@src,@i,1))
 if @j&lt;0x800 select @res=@res+left(@src,@i-1)+char((@j&amp;1984)/64+192)+char((@j&amp;63)+128)
 else select @res=@res+left(@src,@i-1)+char((@j&amp;61440)/4096+224)+char((@j&amp;4032)/64+128)+char((@j&amp;63)+128)
 select @src=substring(@src,@i+1,datalength(@src)-1), @i=patindex(@pi,@src collate Latin1_General_BIN)
 end
 select @res=@res+@src
 return @res
end

CREATE Function Utf8ToUcs(@src varchar(MAX)) returns nvarchar(MAX) as
begin
 declare @i int, @res nvarchar(MAX)=@src, @pi varchar(18)
 select @pi='%[à-ï][€-¿][€-¿]%',@i=patindex(@pi,@src collate Latin1_General_BIN)
 while @i&gt;0 select @res=stuff(@res,@i,3,nchar(((ascii(substring(@src,@i,1))&amp;31)*4096)+((ascii(substring(@src,@i+1,1))&amp;63)*64)+(ascii(substring(@src,@i+2,1))&amp;63))), @src=stuff(@src,@i,3,'.'), @i=patindex(@pi,@src collate Latin1_General_BIN)
 select @pi='%[Â-ß][€-¿]%',@i=patindex(@pi,@src collate Latin1_General_BIN)
 while @i&gt;0 select @res=stuff(@res,@i,2,nchar(((ascii(substring(@src,@i,1))&amp;31)*64)+(ascii(substring(@src,@i+1,1))&amp;63))), @src=stuff(@src,@i,2,'.'),@i=patindex(@pi,@src collate Latin1_General_BIN)
 return @res
end
</code></pre>

I've migrated a database from mysql to SQL Server (politics), original mysql database using UTF8.

Now I read <a href="https://dba.stackexchange.com/questions/7346/sql-server-2005-2008-utf-8-collation-charset">https://dba.stackexchange.com/questions/7346/sql-server-2005-2008-utf-8-collation-charset</a> that SQL Server 2008 doesn't support utf8, is this a joke?

The SQL Server hosts multiple databases, mostly Latin-encoded. Since the migrated db is intended for web publishing, I want to keep the utf8-encoding. Have I missed something or do I need to enc/dec at application level?

How to Use UTF-8 Collation in SQL Server database?

SQL Server

数据库

我已经将一个数据库从mysql迁移到SQL Server (politics)，原始的mysql数据库使用UTF8。现在我在上看到SQL Server2008不支持utf8，这是在开玩笑吗？SQL Server托管多个数据库，其中大部分是拉丁语编码的数据库。由于迁移的数据库是用于web发布的，所以我想保留UTF8编码。...

问如何在SQL Server数据库中使用UTF-8排序规则？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL Server数据库中使用UTF-8排序规则？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL Server数据库中使用UTF-8排序规则？
EN