How to filter out strange characters 2

[Bilberry]

Hi All,

I have imported some data from an AS400 system. The connection string is look like:

Provider=IBMDASQL.DataSource.1;Persist Security Info=False;User ID=****;Password=****;Data Source=10.1.1.5

Some data has been imported to my SQL 2005 database. But now in one of my table, some columns contain strange characters like little squares ?

I need to export these data into a XML file without those strange characters, how can i replace them easily?

Thanks in advance

 

[SQLSister]

That means it contains charaters that aren't part of the normal alphanumeric (and symbols like $) characters. This would be stuff like tabs or carriage returns normally. If you know what character they are you can use replace to replace that ascii character with ''. Other wise you will be stuck removing all possible characters that are outside the range of characters you want in your data. This can be quite time consuming. I would suggest you bring the data into a work table first, clean it up and then put it into your production tables so that you don't have issues later. Cleaning up your existing production tables could be very time-consuming depending on the number of fileds and records involved. Here is some code to get you started,

update table1
set field1 = replace(field1, char(10), '')

You can write something similar to this for every ascii char you want to remove. Start with tab and carriage return and linefeed. Likely this will fix the problem.


"NOTHING is more important in a database than integrity." ESquared

 

[Bilberry]

Hi thanks for your reply, i have tried char(10) and char(13) but it doesnt replace the character. How can i know which character it is in my SQL 2005 environment?

 

[gmmastros]

Another common one... Char(9) <the tab character>.

To find out for sure....

In SQL Server Management Studio, click
Query -> Results To -> Text

Run a select query for the record that is showing the strange character. You will see the results in the text window. You can then copy/paste the weird character in to another select statement to get the ascii value.

[tt][blue]Select ASCII('[COLOR=black yellow]Paste your strange character here[/color]')[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Instead of the ASCII function, you may want to try using the [blue]Unicode[/blue] function too. (just in case).



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Bilberry]

Thanks friends...