Shulou(Shulou.com)05/31 Report--

Hidden in SQL Server field super strange characters how to solve, I believe that many inexperienced people are helpless, for this reason this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

Program architecture: B/S development environment: Windows XP + IIS6 + VS2003 database: SQL Server 2000 part of the function: Export data from SQL Server to Access. This system has been working for more than half a year, but there are still problems with this import and export. The import and export I am responsible for is sandwiched between the network version and the stand-alone version of the system. As long as any side makes changes to the database, this import and export will fail. Alas, there are more things to worry about. Recently, I encountered a very strange problem. Because the import and export before the use of SQL Server support OpenDataSource () function to do, and when encountered 64-bit system will encounter does not support Microsoft.Jet.OLEDB.4.0, so re-encoding, technical steps are to first read data from SQL Server to DataTable, and then traverse the DataTable, for each row, the data will be recombined into an Insert statement, and then open the Access file link, execute the Insert statement just generated, the data will be inserted into Access. The statement I finally submit to Access for execution is spelled out from the data in SQL Server, such as SQL Server: select a, b, c from tblTemp The statement I submit to Access for execution is: Copy the code as follows: Str1 = "Insert into" Str2 = "tblTemp(a,b,c)values(" strResult = str1 & str2 & "'aa', 2, 'cc' " & ")"

The big problem this time is that there is a special character in the b field of the SQL Server table, which prevents my program from spelling out the complete string. The string strResult sometimes does not have the final ")", but strangely, this table has more than 40,000 records in total, and this phenomenon occurs only when combining individual records. However, the submission to Access execution is definitely not passed, prompting SQL syntax errors. I then query the database of this record, query with the query analyzer did not find any special characters, asked a colleague only to know, he said that some of the characters in the table before the customer input to save the carriage return, I suddenly realized, yes, carriage return is invisible ah, so, want to use the following statement query whether there is carriage return in the field: copy code is as follows: select charindex(char(10), demc) from tblgc_jc_de where xh = 15641 select charindex(char(13), demc) from tblgc_jc_de where xh = 15641 select charindex(char(10) + char(13), demc) from tblgc_jc_de where xh = 15641 select charindex(char(13) + char(10), demc) from tblgc_jc_de where xh = 15641

Strangely enough, the returns are all 0, which means they can't be found. This is depressing. What characters are stored in the field? Another colleague taught a trick, directly in the enterprise manager to return all the row records of the table, and then select to view the field, found that there is indeed an extra character: what character is not carriage return? When puzzled, suddenly remembered, it is better to look at the physical data of this table, must be able to find out what characters stored in the field. But the table has 44022 records, check the physical data of a record is a needle in a haystack, how to do??? I just want to query the physical data of this record. How do I do that? Can you put this data into another table, this table only has this record, this can not check it? Oh, this is simple. In fact, I also built a temporary database. This database only has one table. This table only has one record, which contains the problematic record above. The SQL statement used is as follows: -- TYZW is the formal database use TYZW --Create a temporary database, and then insert the problematic record into the temporary database. create database tmpTYZW go select * into tmpTYZW.. tblgc_jc_de from tblgc_jc_de where xh = 15641 go Now it's time to look at the physical data of this record. First, we need to find the location of the table in the physical file in the sysindexes system table. Then we can view the physical data through the dbcc page command:

Now it's time to look at the physical data of this record:

Oh, my God, it's zero. I don't know how it got there. The problem is finally there, but how to solve it? The easiest way to do this is to replace the zero, so test with the following statement:

select replace(demc, char(0), '') as demc from tblgc_jc_de

But no, the reason is that the replace function cannot find the character 0, because it searches by two bytes, so it cannot find this character directly on SQL Server, and the replacement cannot be replaced. But, on second thought, can I use binary to find and replace? Looking at the data, you can find the position of 0 in this field by using the following SQL statement:

Check is able to find out, but I found that the replace function still can not be used, unless it is to replace 4 bits, that is, 0x0038.

In the end, he had no choice but to cut off a text from the location with this special character, which meant that he did not even want the 38:

select

case when charindex(convert(varbinary(1),0x00),convert(varbinary(200),demc)) > 0

then substring(demc,1,len(demc)-1)

else demc

end as DEMC

from tblgc_jc_de

This is the final solution I'm using now, because I've looked up 40,000 records, and only eight have this zero in the field. So even if you cut off a text and affect the function and display of the program.

After reading the above, do you know how to solve the super strange characters hidden in SQL Server fields? If you still want to learn more skills or want to know more related content, welcome to pay attention to the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.