How to deal with half Chinese characters in SQL Server2000

server| Chinese Characters | problem


/* Written by Enydraboy, 2003-07-17 * *


/* Published in CSDN * *


/* Reprint Please specify the source and retain this copyright information * *





in CSDN Expert Forum, see a friend asked about "because the data imported from other databases there is half the problem of Chinese characters, so I hope that in the query statement in this half of Chinese characters, how to deal with all the line." Hope you master generous enlighten! "The problem, Qi a bit unexpected, but later felt that from this problem can bring out some of the SQL Server Chinese character storage and processing methods, feel the need to sum up.





characters are stored in two bytes, and the values of each byte are >127. So the solution to the problem above is to intercept the string in byte order and discard it when the first byte is >127, but the latter byte is <127.





Next, we need to be clear about technical details:





(1) has a number of string-related operational functions in SQL Server, but it is problematic to directly manipulate strings containing Chinese characters. The description is as follows: Len calculates the length, and the Chinese characters and the English characters are considered to be of the same length; substring also, ASCII is the ASCII code that returns the first byte.





Example:





Select Len (' Hello a '), substring (' Hello a ', 1,1), ASCII (' You ')





result is





                            





-----------       ---- -       ----------





3 You 196





Be sure to convert the string into varbinary to handle it.





 





(2) SQL Server, how to combine the ASCII code into Chinese characters, the high byte and the bottom byte are converted into characters and then spliced together. such as char (210) +char (166) is the Chinese character Yao.





the above technical details, then the problem will be solved. I write a function to solve the problem.





Create function Fn_truncsemihanzi (@str varchar (4000))





returns varchar (8000)





as





begin





Declare





@sTmp varchar (8000), @i int, @itmp int, @itmp2 int, @stmp2 varchar (2)





Select @sTmp = '





Select @i=1





while @i<=len (@str)





begin





Select @itmp =convert int,substring (CONVERT (varbinary,substring (@str, @i,1), 1, 1)--intercepts a byte





if @itmp >127





begin





--Greater than 127 check for a later 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)--Discard half Chinese characters





End





End





Else





begin





Select @sTmp2 =char (@itmp)





End





Select @sTmp = @sTmp + @stmp2





Select @i=@i+1





End





return @stmp





End





test is as follows:





DECLARE @str varchar (4000)





--Setting parameter values





--Setting parameter values





--with half Chinese characters, is the front part of your character





Select @str = ' B ' +char (CONVERT (int,substring (varbinary, ' you '), 1,1)) + ' A You '





Select @str with half Chinese characters, Dbo.fn_truncsemihanzi (@str) Remove half Chinese characters





 





With half Chinese characters length to remove half Chinese characters length





------------------ ----------- --------------- -------------------





b 腶 You 3 BA you 3





 





We can also find such an interesting phenomenon, because I was truncated half of the Chinese characters out, the result of your first half of the characters and the next letter a combination of English, became a strange word Fu Yi.





summed up, through this problem, we can understand the Chinese characters in the SQL Server2000 storage and processing of the basic methods, such as how to distinguish whether a string contains Chinese characters, and separate strings in both Chinese and English can be applied to the method in this article.


the environment tested in this article is Win2000 Server (Chinese) +sql Server 2000 (Chinese), there is no test in English environment, if there is a problem, please correct me.