Google Answers Logo
View Question
 
Q: Utility to find ascented characters ( Answered 5 out of 5 stars,   3 Comments )
Question  
Subject: Utility to find ascented characters
Category: Computers > Programming
Asked by: fabios_ss-ga
List Price: $10.00
Posted: 19 Sep 2006 22:06 PDT
Expires: 19 Oct 2006 22:06 PDT
Question ID: 766852
Hello Everyone,

What I am doing is deploying users and billing codes down to a Copier.

The device device does not supported ascented characters.

i.e

    

I am not sure what font they are or what language but I am assuming
there is a full alphabet of them.

So what I want to be able to do is have a utility which can query a
text file or a field in a SQL database that can detect for these types
of characters.
Once detected it can somehow flag it. i.e to an output file, email etc.

Do you think what I am trying to do is feasible?

Thanks.

Request for Question Clarification by aht-ga on 20 Sep 2006 21:23 PDT
fabios_ss-ga:

Accented characters (such as , , , ), actually live in the
extended portion of the ASCII character set. You can learn more here:

http://en.wikipedia.org/wiki/Extended_ASCII
http://en.wikipedia.org/wiki/ISO_8859-1

So, the short answer to your question is yes, it is feasible... all
you need is either a program to examine the text file, or a SQL query
to examine the contents of a field in a SQL database, and upon
detecting a character with an ASCII code value greater than 128, take
whatever action you want taken.

The reason I am posting this as a Request for Question Clarification,
though, is because I suspect that you are not just wondering if it is
feasible, but rather want to be pointed towards a solution. So,
perhaps you can provide some more information that might allow for an
answer that you will find satisfactory?

For example:

- are the user names and billing codes stored in a SQL database, and
if so, do you have complete access to the database?
- are you familiar with composing and executing SQL queries?
- is this a one-time exercise you are thinking of, or something that
you anticipate needing to do regularly?

Regards,

aht-ga
Google Answers Researcher

Clarification of Question by fabios_ss-ga on 21 Sep 2006 03:36 PDT
Hi,

Thanks for your response. You have hit it on the head.

Answers to your questions.

- are the user names and billing codes stored in a SQL database, and
if so, do you have complete access to the database?

Yes they are. These values are all stored in a table.
The field is called "name"

- are you familiar with composing and executing SQL queries?

I can perform select and insert statement but I do have access to a
SQL developer if things get tricky. :)

- is this a one-time exercise you are thinking of, or something that
you anticipate needing to do regularly?

I would like to do it regulary. Something like an SQL trigger would be
good. i.e maybe trigger an event viewer warning or some sort of
notification. I would like to have this run daily.

Let me know your thoughts.
Answer  
Subject: Re: Utility to find ascented characters
Answered By: aht-ga on 23 Sep 2006 22:10 PDT
Rated:5 out of 5 stars
 
fabios_ss-ga:

Thank you for the clarification.

If I understand the situation correctly, you have

1) A list of users and billing codes stored in a SQL database, and

2) On a regular basis, you need to send the copier a current list of
users and their billing codes (presumably so that the users can select
their account in order to use the copier), and

3) The copier is unable to display accented characters, most likely
displaying some strange block character, or nothing at all.

There's a couple of possible ways to solve this.

---------------------------
The first way would be to leave the SQL data alone, and just 'clean'
the extracted list before sending it to the copier. This can be very
simple if the list is in the form of a text file (for example, a TXT
or CSV file), and if you are willing to perform a single manual task
each time you send the list to the copier.

Assuming you are working on a Windows computer, then using a wonderful
freeware text editor called PSPad, available from
http://www.pspad.com, you can take advantage of a built-in function
that replaces all accented characters with their non-accented version.
To see what I mean, please download and install PSPad from:

http://www.pspad.com/en/download.php

After you install it, run it and open up any text file that you may
have, which contains accented characters (for example, a text version
of the list of user names and billing codes). Now, go to the Edit
menu, and select Special Conversion>Remove Accent from char. After you
confirm that you want to remove accents from the entire document, the
editor will proceed to do just that, leaving you with a text file
ready to be saved, with no accents in it.

This method does require some manual work by you, though. Since you
mentioned that you want to have something that runs daily, that
implies to me that you are updating the copier daily; if so, then this
method might be too labor-intensive for you in the long run. It is
simple, though, so even if you choose not to use it, I suggest you
keep the PSPad editor around for times when you might need to manually
manipulate the list before sending it to the copier.

---------------------------
The second way to solve this problem would be to handle this all
within the SQL world. You can either validate new entries to detect
accented characters and reject them, or you can search for (and
replace) accented characters whenever you are generating the current
user/code list for the copier. This approach is not that easy, as the
handling of accented characters in SQL can be tricky. If you want to
handle it in SQL, then definitely engage your SQL developer to help
you with the actual coding. To help the developer with his or her
task, please consider the following.

First, there are many different versions of SQL out there, ranging
from MS SQL and mySQL, to postgreSQL and Mimer SQL (visit
http://en.wikipedia.org/wiki/SQL for more info if you are interested).
With so many variants, there are bound to be differences in the
solution. Your developer, though, should be able to follow along with
the following information and provide you with the exact solution that
works in your specific database, on your specific SQL server.

If you do not want accented characters to live in the table with the
user names, then the best thing to do is to trap it when you are
entering new users. If you are using a form to enter the data, and a
validator to check the data, then your developer can use an example
similar to the approach described here to detect and remove accents:

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/726b76c4ceeb3b8b/741233aa55ec0153?lnk=st&q=sql+accents&rnum=3&hl=en#741233aa55ec0153

(yes, it's a long link, unfortunately)

In particular, see the post by 'Daniel Blais' where he provides a
sample SQL FUNCTION declaration for a new function called
replaceAccentChar. This function can be used to return an accent-free
version of any string of up to 255 characters (please note that if the
'' character matters, your developer needs to add one more line to
the function, after the while...end loop:

set @temp = Replace(@temp, '', 'AE')


Now, if you actually want to leave the accents in the user names in
the SQL database, then your developer should be able to modify your
script for extracting the usernames/billing-codes into a list, so that
all of the usernames get fed through this new function, leaving you
with a clean list. If you have already automated the creation of the
list on a daily basis, then adding this function will seamlessly allow
you to continue with your current practice, without having to worry
about accents in the output.

---------------------------

I hope that one of these two methods helps solve the problem for you.
Accented characters in SQL databases cause quite a lot of grief for
SQL programmers (just do a Google Groups search on 'sql accents' to
see what I mean... http://groups.google.com/groups/search?hl=en&lr=&q=sql+accents
). Fortunately, your case is not as complex as some of the ones you
can read about in the results of that search.

Best of luck with this! Please let me know if any part of this is
unclear, and also let me know if this works for you!

Regards, 

aht-ga
Google Answers Researcher
fabios_ss-ga rated this answer:5 out of 5 stars and gave an additional tip of: $1.00
Thankyou very much. You have given me more than enough information to
move forward on this!

Comments  
Subject: Re: Utility to find ascented characters
From: probonopublico-ga on 20 Sep 2006 00:31 PDT
 
Have you thought of using a sniffer dog?
Subject: Re: Utility to find ascented characters
From: fabios_ss-ga on 20 Sep 2006 19:49 PDT
 
I have not heard of a sniffer dog.

I am happy to look into this if you can help point me in the right direction :)
Subject: Re: Utility to find ascented characters
From: steveinfinland-ga on 23 Sep 2006 22:27 PDT
 
In that case, the breed of dog is a pointer.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy