/ * Written by Enydraboy, 2003-07-17 * // * Published in 9cbs * // * Reprinted please indicate the source and keep this copyright information * /
In the 9CBS expert forum, I saw a friend asking about "there is a problem with the data imported from other databases, so I hope to handle this half of Chinese characters in the query statement, how to deal with I hope that everyone is enlightened! "The issue is a bit surprised, but later I feel that I can bring out some ways to store and process in the SQL Server Chinese characters, I feel that it is necessary to summarize.
Chinese characters are stored by two bytes, and each byte is> 127. So the solution to the above problem is: intercepted the string in byte sequence, when the first byte is> 127, but the later one byte is the case of <127, it will be discarded.
Next, we have to clarify some technical details:
(1) There are many string related operational functions in SQL Server, but directly to include the character string of Chinese characters, it will be a bit problem. The following: When the LEN calculates the length, the Chinese characters and English characters will be as long as the length; Substring is the same, and ASCII is the ASCII code that returns the first byte.
EXAMPLE:
SELECT LEN ('Hello A'), Substring ('Hello A', 1, 1), ASCII ('You')
turn out
-------------- - ----------
3 you 196
Be sure to convert a string to varbinary if you can.
(2) SQL Server, how to combine the ASCII code into Chinese characters, which is to transfer the high byte and the bottom byte to characters. Such as Char (210) char (166) is Yao this Chinese character.
After clearing the above technical details, you can solve the problem. I solve the problem by writing a function.
Create Function Fn_Truncsemihanzi (@str varchar (4000))
Returns varchar (8000)
AS
Begin
Declare
@stmp var, @ itmp int, @ itmp2 int, @ stmp2 varchar (2)
SELECT @ stmp = ''
SELECT @ i = 1
While @i <= len (@STR)
Begin
SELECT @ itmp = Convert (int, Substring (Convert (varbinary, @ I, 1)), 1, 1)) - Intercepting a byte
IF @itmp> 127
Begin
- More than 127 check behind one byte
SELECT @ itmp2 = Convert (int, Substring (Convert (varbinary, substring (@str, @ i, 1)), 2, 1))
IF @ itmp2> 127
Begin
SELECT @ stmp2 = char (@itmp) char (@ itmp2) - is a complete Chinese character
end
Else
Begin
SELECT @ stmp2 = char (@ itmp2) - Drop half of Chinese characters
end
end
Else
Begin
SELECT @ stmp2 = char (@itmp)
end
SELECT @ stmp = @ stmp @ stmp2select @ i = @ i 1
end
Return @stmp
end
The test is as follows:
Declare @str varchar (4000)
- Set the parameter value
- Set the parameter value
- With half of Chinese characters, it is part of your Chinese characters.
SELECT @ Str = 'B' Char (Convert (int, Substring (Convert (Varbinary, 'you'), 1, 1))) 'a you'
Select @str with half a Chinese character, dbo.fn_truncsemi zi (@str) removes half of Chinese characters
Leave half a Chinese character length with half a Chinese character length
---------------------------------------------- -------------
B 腶 You 3 bass 3
We can also find such a interesting phenomenon, because I cut half of the Chinese characters, the result of your first half of the Chinese characters and the back of an English letter A, became a weird character.
Summary, through this problem solution, you can understand the basic method of Chinese characters in SQL Server2000, like how to distinguish whether Chinese characters are included in a string, and in Chinese and English in the Separate string can be used in this article.
The environment tested in this article is Win2000 Server (Chinese) SQL Server 2000 (Chinese), there is no test in English, if you have any questions, you are welcome to correct.
