Mssql Chinese Character Processing (character set encoding and sorting rules)

& Nbsp; mssql Chinese Character Processing (character set encoding and sorting rules) sqlserver Chinese processing involves character set encoding and sorting rules, which is a very tangled issue. Sqlcode -- ascii character selectn, xcast (nasbinary (2), unchar (n) fromnumswherenbetween32and126 -- unicode Chinese Character select mssql Chinese Character Processing (character set encoding and sorting rules)
Sqlserver Chinese processing involves character set encoding and sorting rules, which is a very tangled issue.

SQL code

-- Ascii character
Select n, x = cast (n as binary (2), u = nchar (n) from nums where n between 32, and 126
-- Unicode Chinese Character
Select n, x = cast (n as binary (2), u = nchar (n) from nums where n between 19968 and 40869
19968 0x4e00 1
40869 0x9fa5
-- The following two conditions are used to determine whether a string contains Chinese characters.
Like n' % [-] % 'collate chinese_prc_ci_as
Like n' % [1-] % 'collate chinese_prc_bin
-- This is because the Chinese character sorting order is different under the preceding two different sorting rules.
-- Chinese fullwidth punctuation
Select n, x = cast (n as binary (2), uq = nchar (n), ub = nchar (n-65248) from nums where n between 65281 and 65374
Select nchar (12288), nchar (32)
65281 0xff01! !
65374 0xff5e ~ ~
-- The following conditions are used to determine whether a string contains full-width punctuation.
Like n' % [! -~] % 'Collate chinese_prc_bin

Conversion of full-width and halfwidth punctuation:

SQL code
-- Full2half
Create function [dbo]. [full2half] (
@ String nvarchar (max)
)
Returns nvarchar (max)
As
/*
Full Width (fullwidth) to half width (halfwidth)
*/
Begin
Declare @ chr nchar (1)
Declare @ I int
Set @ string = replace (@ string, n'', n '')
Set @ I = patindex (n' % [! -~] % 'Collate latin1_general_bin, @ string)
While @ I> 0
Begin
Set @ chr = substring (@ string, @ I, 1)
Set @ string = replace (@ string, @ chr, nchar (unicode (@ chr)-65248 ))
Set @ I = patindex (n' % [! -~] % 'Collate latin1_general_bin, @ string)
End
Return @ string
End
Go
Create function [dbo]. [half2full] (
@ String nvarchar (max)
)
Returns nvarchar (max)
As
/*
Convert half width to full width)
*/
Begin
Declare @ chr nchar (1)
Declare @ I int
Set @ string = replace (@ string, n'', n '')
Set @ I = patindex (n' % [! -~] % 'Collate latin1_general_bin, @ string)
While @ I> 0
Begin
Set @ chr = substring (@ string, @ I, 1)
Set @ string = replace (@ string, @ chr, nchar (unicode (@ chr) + 65248 ))
Set @ I = patindex (n' % [! -~] % 'Collate latin1_general_bin, @ string)
End
Return @ string
End
Go