Home » SQL & PL/SQL » SQL & PL/SQL » How to eliminate illegal characters in SQL
How to eliminate illegal characters in SQL [message #281132] Thu, 15 November 2007 18:03 Go to next message
daconfora
Messages: 5
Registered: November 2007
Junior Member
This is a pretty broad question but it is slowly becoming a major problem for our company. Any help would be much appreciated.

Thanks,

Dane
Re: How to eliminate illegal characters in SQL [message #281136 is a reply to message #281132] Thu, 15 November 2007 18:09 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Which law did the illegal characters violate?

Don't allow "illegal characters" into the DB in the 1st place.


Re: How to eliminate illegal characters in SQL [message #281137 is a reply to message #281132] Thu, 15 November 2007 18:12 Go to previous messageGo to next message
daconfora
Messages: 5
Registered: November 2007
Junior Member
We are having trouble matching new reports to master lists because of randomly generated illegal characters in both the master and the new reports. So I want to eliminate all illegal characters in the master while also filtering all new reports we upload.

Thanks,

Dane
Re: How to eliminate illegal characters in SQL [message #281139 is a reply to message #281132] Thu, 15 November 2007 18:17 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
EXACTLY how does one differentiate the "legal" characters from the "illegal" characters?

are the illegal characters to be converted to legal characters (which)?

Are illegal characters to be simply eliminated from the field in which they occur?
Re: How to eliminate illegal characters in SQL [message #281140 is a reply to message #281132] Thu, 15 November 2007 18:21 Go to previous messageGo to next message
daconfora
Messages: 5
Registered: November 2007
Junior Member
We would like them to be eliminated and allow legal characters to slide over in their place.

Thanks,

Dane
Re: How to eliminate illegal characters in SQL [message #281141 is a reply to message #281132] Thu, 15 November 2007 18:24 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
EXACTLY how does one differentiate the "legal" characters from the "illegal" characters?

EXACTLY how does one differentiate the "legal" characters from the "illegal" characters?

EXACTLY how does one differentiate the "legal" characters from the "illegal" characters?

EXACTLY how does one differentiate the "legal" characters from the "illegal" characters?
Re: How to eliminate illegal characters in SQL [message #281143 is a reply to message #281141] Thu, 15 November 2007 18:35 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
calm down ana and give the man what you think he wants...

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2612348048
Re: How to eliminate illegal characters in SQL [message #281145 is a reply to message #281132] Thu, 15 November 2007 18:48 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
What if the unwanted characters are valid UTF-8 characters?

IMO, no clean up effort should be undertaken, unless & until every one is sure that no additional bad characters can get into the DB.
Otherwise nightly a job will have to be run against all data that changed in the last 24 hours; assuming that said changes can be identified.
Re: How to eliminate illegal characters in SQL [message #281146 is a reply to message #281145] Thu, 15 November 2007 18:50 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Lets just assume that he want's only 7-bit ASCII printable characters for now...
Re: How to eliminate illegal characters in SQL [message #281225 is a reply to message #281146] Fri, 16 November 2007 03:21 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
andrew again wrote on Fri, 16 November 2007 00:50

Lets just assume that he want's only 7-bit ASCII printable characters for now...

Hmmm, would it not just be better (I was going to say easier but based on the direction this thread is going...) to get the OP to tell us his actual requirement rather than assuming that he wants a specific characterset. What if he just wants to 'ban' chr(10) but only if it is 3 characters away from another chr(10) which in turn must be next to the letter s. (Ridiculous scenario I know, but I'm trying to make a point) Ana repeatedly asked the same question, but the OP deemed it unworthy of response. Personally I always become suspicious of such an approach as it implies that there is a much bigger requirement underneath. I get it, we're here to help people, but surely we have the right to expect answers to our questions when we ask them in the context of assisting? In short, I'm with Ana on this one. Get the OP to answer what constituites an illeagal character in HIS scenario.
My Tuppence Ha'penny (that and a couple of quid'll get you a coffee at Costa)
Re: How to eliminate illegal characters in SQL [message #281392 is a reply to message #281132] Fri, 16 November 2007 11:55 Go to previous messageGo to next message
daconfora
Messages: 5
Registered: November 2007
Junior Member
Hey all, sorry to create such a heated discussion. We want to eliminate all characters that aren't translating between ASCII and UTF-8. Here are some examples of what is happening.

Aganjú becomes Aganj£ during our load into sql

When we try to convert it this is the error we get.

select convert('Aganj£' using utf8_to_ascii)
ERROR: character 0xa3 of encoding "MULE_INTERNAL" has no equivalent in "SQL_ASCII"
SQL state: 22P05

The files we are loading are generally in .csv format. Is there some noted ASCII/UTF-8 relational error between .csv and sql. Any help would be greatly appreciated.

Thanks,

Dane

Re: How to eliminate illegal characters in SQL [message #281394 is a reply to message #281392] Fri, 16 November 2007 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Aganjú becomes Aganj£ during our load into sql

It is not clear if you have this in database and lost the character during unload to a csv file.
Or if you have this in a UTF8 file and lost the character during the load.

In both cases, you should be able to get the correct characters if you correctly use the products.

Regards
Michel

[Updated on: Fri, 16 November 2007 12:09]

Report message to a moderator

Re: How to eliminate illegal characters in SQL [message #281395 is a reply to message #281132] Fri, 16 November 2007 12:09 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>select convert('Aganj£' using utf8_to_ascii)
HUH?
IMO, this is NOT valid syntax.
Please elaborate by showing actual function PL/SQL code doing the data load operation.

By "ASCII" do you mean only "legal" characters are CHR(0) to CHR(127) inclusive?
Re: How to eliminate illegal characters in SQL [message #281410 is a reply to message #281132] Fri, 16 November 2007 13:54 Go to previous messageGo to next message
daconfora
Messages: 5
Registered: November 2007
Junior Member
anacedent,

We are using PostgreSQL 8.2.1, that may be your problem.

Michel Cadot,

The master file that we are using as a reference is an aggregation of .csv files which we loaded into sql. We are trying to match flat files converted to .csv with that master file. So far we matched around 400k rows but we have 100k that have illegal characters and don't match precisely. As far as I can tell the flat to .csv's conversion files match visually with the master file but when we load then into sql the software is changing and in some cases adding illegal characters.

Thanks,

Dane
Re: How to eliminate illegal characters in SQL [message #281411 is a reply to message #281410] Fri, 16 November 2007 14:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ what is the character set of the file?
2/ what is the tool you use to load
3/ what is your database? This is Oracle, doesn't it?

Regards
Michel
Re: How to eliminate illegal characters in SQL [message #281412 is a reply to message #281411] Fri, 16 November 2007 14:08 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
How does PostgreSQL 8.2.1 get onto an Oracle forum without clearly stating upfront that this has nothing to do with Oracle?
Re: How to eliminate illegal characters in SQL [message #281414 is a reply to message #281132] Fri, 16 November 2007 14:28 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Too bad for all concerned you can't or won't post coherent statements about your environment or problem.

Here is what we do NOT know.
Operating System name & version
Oracle version to 4 decimal place.
What s/w is being (ab)used to load the data.
How to identify "legal" characters?
How to identify "illegal" charcters?

OK, you have a problem.
If you decide to provide useful facts beyond repeating things don't match, maybe somebody might be able to suggest an actual solution.

Until then, Your On Your Own (YOYO)!
Re: How to eliminate illegal characters in SQL [message #281419 is a reply to message #281132] Fri, 16 November 2007 16:02 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
If I was you, I would post your question at


http://www.dbforums.com/


This forum is ONLY for the oracle database.
Previous Topic: Create Directory
Next Topic: Question on user defined functions
Goto Forum:
  


Current Time: Sun Dec 11 04:20:20 CST 2016

Total time taken to generate the page: 0.08837 seconds