need to kow how to search for high ascii values in a database

[Guest_imported]

I have a production database that is getting invalid characters put into it by the clients somehow. I need to be able to search select parts of the database (for locations that are having problems) and find all characters that are above the normal ascii set. Does anyone know a way to perform this search?

ex. I want to find all values that have an ascii value over 179

Thanks,
Azal

 

[foxdev]

Generally, it seems like you'd create an SP that, for a specific column (to keep it simple), creates a cursor for scanning, and uses SUBSTRING() along with a counter to go through each character up to LEN(thiscolumn) and checks the ASCII() value.

Or you could do something like:

SELECT MyPrimaryKey FROM MyTable WHERE MyColumn LIKE '%³%'

but you'd have to set up a loop to go through all the target characters.

Long-term, though, it seemingly would be better for the client app to filter these at the origination point.

 

[Guest_imported]

Well I could run a search on the database late at night when no new data is entering it, but it is a very large database so I was hoping that there was a way to just look at each character in the text fields and say

if the ascii value is greater than 179 (or any number) then show me where it's occuring.

I know most programmign languages would allow for something like that.. but I'm still learning SQL server and haven't been able to find a way to do that yet.

Thanks,
Azal

 

[Guest_imported]

Also, the client is using a different character set than we are. We're using standard ascii and they're using something else.. I can't recall right now what it's called but for instance they will type in
[U/P
and it comes across to our SQL database as
~
OU/I
(pretend that that tielde is over top of the O)

Now I guess that their byte code for a bracket is SQL servers byte code for an O with a tielde over it?

Azal

 

[Guest_imported]

I found out they are using EPCDIC on their end.. a different character set apparently. I have no EPCDIC experience.

 

[Guest_imported]

EBCDIC is the character encoding IBM uses on their midrange and mainframe computers. The download or data transfer software can easily convert EBCDIC to ASCII. Ask if the guys on the other end can do this for you. EBCDIC has a one to one mapping with ASCII. For example space is 20 in ASCII and 40 in EBCDIC. If for some reason you have to convert it go to IBM.COM and their knowledge base to find an EBCDIC to ASCII translation table.