| Author |
Topic |
|
crugerenator
Posting Yak Master
126 Posts |
 Posted - 2011-03-15 : 11:01:01
|
| I'm having a hell of a time figuring out what special char is stored in a field in my DB. I should actually say unrecognizable char - it's the little square that shows up in SQL when you view table. It should be a line break or a carriage return, chr(10)+chr(13), but I can't tell for sure. Is there a certain select statement I can use to display what those chars are? I wish I could just check the code that stores the values in the database, but it's a 3rd party tool and I only have access to the data, not the source for their app. Any help is appreciated. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-03-15 : 11:26:48
|
| Select * from tablewhere junk like '%'+char(10)+'%'JimEveryday I learn something that somebody else already knew |
 |
|
|
crugerenator
Posting Yak Master
126 Posts |
Posted - 2011-03-15 : 12:33:06
|
| yeah, I can find that, and by working backwards I think I can figure out what the char values are, but I was hoping there was a way to get an actual output.I know this returns results for data that looks good:Select * from tablewhere junk like '%'+char(10)+char(13)+'%'and that data that looks bad doesn't return any results if I included char(13) but it does if I'm looking for only char(10). |
|
|
|
mandm
Posting Yak Master
120 Posts |
Posted - 2011-03-15 : 12:42:34
|
| Could you convert to VARBINARY and look at the hex values of the string? Or if the string is too long SUBSTRING the portion that has the character and convert that? That should tell you what the character is. |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-03-15 : 12:53:14
|
| You could play around with this. The problem is to really find everything, you have to scroll thru every character found after PATINDEX('%[^A-Z]%',col1)DECLARE @Table TABLE (Col1 varchar(5))INSERT INTO @TABLESELECT 'ABCD' + CHAR(10) UNION SELECT 'ABC' + CHAR(10) +CHAR(13) SELECT ASCII( SUBSTRING(Col1,PATINDEX('%[^A-Z]%',col1),50)),ASCII( SUBSTRING(Col1,PATINDEX('%[^A-Z]%',col1)+1,50))FROM @tableJimEveryday I learn something that somebody else already knew |
|
|
|
crugerenator
Posting Yak Master
126 Posts |
Posted - 2011-03-15 : 12:55:11
|
| I think the problem is actually storing data in columns with different data types. The field I grab data from, and use to populate a soap call is of type varchar. The data is the stored in a column of type text. Do I have to convert carriage returns from varchar to another type for text? |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-03-15 : 13:04:03
|
| You can't use like on TEXT, so you'd have to convert the text to varchar to do anything with it. The problem is the deprecated TEXT data type that also has junk in it. I get rid of that junk when importing.JimEveryday I learn something that somebody else already knew |
|
|
|
crugerenator
Posting Yak Master
126 Posts |
Posted - 2011-03-15 : 13:17:27
|
| Hmmm - I never knew that you couldn't use like on columns of data type text. I guess I can't trust the results I'm getting then. I think I have narrowed it down, and it might actually be XML stripping out the CRLF during the soap call, but just as an example, how would you get data from a varchar column to a text column while preserving carriage returns and line feeds?ex.In VARCHAR(MAX):this is line 1 + char(13) + char(10) + this is line 2 + char(13) + char(10) + this is line 3.^ the char(13)+char(10) shows as squares in table view. Would I literally be able to insert the above string into a text column and it would preserve the carriage return and line feeds?ex.select <VARCHAR column above> into NEWTABLENAME(column type of text) from OLDTABLENAME |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
crugerenator
Posting Yak Master
126 Posts |
Posted - 2011-03-15 : 22:43:48
|
| Heh, anything that shows up as a square in SQL when I open the table and look at the data.I actually figured out it was the XML stripping out the vb carriage return and line feed when the SOAP call was received the by the web service I was calling. Thanks for the help though - I learned a lot about how SQL handles line breaks. My solution was to replace all CrLf chars (chr(13)&chr(10) in vbscript) with a unique string before forming the SOAP body. I then passed that to a web service I wrote in c#, which is what the 3rd party tool is built with, just to replacee the unique string with Environment.NewLine, which is c#'s platform independent CrLf. Then, I sent the data to the 3rd party tool through their webservice, and had no issue. Everything was fine with the SQL, it was an issue with consuming .net web services with classic asp. |
|
|
|
|
How to tell what special chars are stored?