Finding/replacing ASCII control characters

[mkal]

I have two queries:
Select description --description field datatype is ntext
From tableA
Where description like '%' + char(1) + '%'
and
Select description
From tableA
Where description like '%
%''

where char(1) =


The first query returns numerous row where as the second returns none, can anyone explain this behaviour. I am trying to find and replace control characters with an empty set but I am having trouble finding them all, not to mention replacing them.

 

[Pattycake245]

You can't query a special character such as this by it's representation in the table. That is just Enterprise Manager's way of displaying special keystrokes like linefeeds, etc. If you queried this through QA you wouldn't see these characters. In QA, if it was a linefeed character, and you ran your query in text mode, it would put that field on separate lines. You have to query as a char(1) field as you have done. There are websites out there with ascii tables that if you needed you could create a table of all these ascii codes and loop through all your fields to see what fields have these keystrokes, if you are looking to "clean" your table(s).

Hope that helps.

Tim

 

[mkal]

I kind of suspected as much but it's nice to have it confirmed. One of the reasons I'm removing these characters is our linux servers wig-out when trying to display them but, they don't seem to mind ASCII characters from char(128)- char(255), is this because they can actually be represented by the servers collation set, and if so, how come I don't see them being displayed even though the a query like the first one I sent returns rows saying they exist.

Thanks,
Mike

 

[earthandfire]

Control characters have an ASCII value of less than 32 (the space character).