ASCII Characters - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

ASCII Characters

Author

Topic

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2005-09-27 : 19:25:02

Hello,
Can anyone help me to write a simple script which will test Ascii characters and if there is any non Ascii character then it should not save in the database.For an example i would be using Excel or Word using all types of characters and if those characters are not ascii charcters then they should not be saved in the database.Ascii characters are like all Keyboard characters. Let me know if you have any more questions.Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-28 : 00:35:04

If you use Front End Application, then check there for proper characters and send that to table

Madhivanan

Failing to plan is Planning to fail

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2005-09-28 : 08:50:09

Hello,
This is what i will be doing i will be putting some ASCII characters on the front end and then i wanted to run a script on the database that non ascii characters have been filtered out before saving them in the table. Can you please give me a script for that.Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-28 : 09:06:20

See Ascii values here
http://www.newebgroup.com/academy/tables/ascii.htm

In your application check for these and send valid data to table

Madhivanan

Failing to plan is Planning to fail

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2005-09-28 : 09:11:12

Hello,
Thanks but i know the Ascii table and i am sending Ascii as well as non Ascii values to the table. I can see that it is filering non ascii values but i thought sometimes you miss something by looking at it so may be i run a small script saying that if value is not out of these values then do not save something like that. Can you help me in a script please.Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-28 : 09:20:05

What is your Front End Application?
You should check the validity of data at the Front End. Dont let SQL Server do this

Madhivanan

Failing to plan is Planning to fail

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2005-09-28 : 09:32:03

Hello,
Its .NET Thanks

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-09-28 : 09:37:28

Mad is correct. Your front end should be filtering the data it sends. NEVER send KNOWN BAD DATA to the database.

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-28 : 09:41:51

I agree with Mad and Don. However, since this is a sql forum...And sometimes your business rules say store whatever the user enters and display a filtered version. So anyway:

There are a lot of ways to attach this. One way is to identify all the non-acceptable characters and replace them with an empty string. One way to do that is with a nested replace function. You'll need as many nesting levels as characters to replace.

--This is a surprisingly fast way to select "condensed" values from a table
--but you need to anticipate ALL "bad" characters.
select 
 replace(
  replace(
   replace(@myVarcharValue, '!', '')
  ,'@','')
 ,'#','')

Another approach is to loop through a value with a while loop and only keep "acceptable"
values. There have been a lot of postings for these techneques. Here is one from a "very clever developer":
STRFILTER: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51291

Spirit1 has posted some great "non-functionalized" regular expressions that do similar things but I
can't find any right now. Try some searches.

Be One with the Optimizer
TG

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-09-28 : 09:53:56

"I agree with Mad and Don. However, since this is a sql forum...And sometimes your business rules say store whatever the user enters and display a filtered version. So anyway:"

If the business rules state "Store whatever the smacktards enter", then there IS no bad data. DISPLAY a filtered version = filter it at the PRESENTATION LAYER.

<sarcasm>
No, i am not bitter, nor do i deal with this every day

</sarcasm>

However, if your boss becomes unreasonable and wants you to do it HIS way, then you have a good point TG.

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources