Originally posted by Paul Sturrock:
What do you mean by "special character"?
A computer-representable character that is not alphabetic, numeric, or blank.
Non-alphabetic or non-numeric character, such as @, #, $, %, &, * and +.
Originally posted by Ulf Dittmer:
While there are some ways of doing this in the database, it would be a much cleaner design not to store them there in the first place. What prevents you from removing those characters before you store them?
But is there any ohter way of doing this apart from using RegEx? I mean, say any special kind of statement or something of that nature.
A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Originally posted by Stan James:
What's the real problem you're trying to solve? What happens when "bad" characters get into the database? Believe me, I've hit dozens of ways that unexpected user input can crash an application, so I appreciate the problem. But just removing them may not be the answer.
A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Originally posted by Paul Campbell:
Really the question is not how to avoid entering special characters in a database... it is how to avoid upserting characters that are not supported by the database's configuration. It isn't just special characters, carriage returns, etc. If the character is not supported by your database's configuration, it becomes unprintable, unreadable, and most of all... it makes that column virtually useless in a where clause, causes odd results in group by, order by, etc. As a result, if you don't eliminate the bad characters prior to committing them to the database, you (or somebody) will have to clean them up later. I was the person that had to clean up the bad data the other day when I had to create a package to remove unprintable/unreadable characters not supported by an Oracle installation using US7ASCII... in this case the user had loaded thousands of rows of data containing "curly" quotes from an excel spreadsheet to the database.
So it is a valid concern and no, I don't think you can do it in a single statement. In my case, the package is now part of the upsert sql and strips the characters as part of the sql statement.
Hoo hoo hoo! Looks like we got a live one! Here, wave this tiny ad at it:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
|