Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Finding/replacing ASCII control characters

Status
Not open for further replies.

mkal

Programmer
Joined
Jun 24, 2003
Messages
223
Location
US
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.
 
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
 
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
 
Control characters have an ASCII value of less than 32 (the space character).

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top