Restrict certain 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

SQL Server Administration (2000)

Restrict certain characters

Author

Topic

JackOfAllTrades
Starting Member

12 Posts

Posted - 2008-08-15 : 17:46:06

I'm looking for a means to restrict certain data/asci characters. :, #, $, %, @, ~ from certain data fields. You see, alpha numeric would be fine for a VarChar datatype field.

Any assistance pointing me in the right direction will be appreciated.

JackOfAllTrades
Starting Member

12 Posts

Posted - 2008-08-15 : 17:48:54

I looked at using constraints but can't figure out how to limit extended characters or the ones I list in the previous post. I have been successful using some constraint code I found that would limit Alpha or Numeric values in a text field, So... I know how that part works at least.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-16 : 02:53:10

Is nt it better to do this validation at client side using some javascript functions before you pass the character values to db for saving?

JackOfAllTrades
Starting Member

12 Posts

Posted - 2008-08-18 : 13:18:31

I don't know that it's better to do at the web front end, but I want to impose both security concepts via DB rules/constraints and by proposed good practices by our web developers. Web fronts using Java script is just one means of updating a database. Alas, I don't control of all the web servers/developers that have ability to update data via those means. So, I'd like to impose another layer at the table or data field.

Colt's, Ruger's, Dan Wesson, & Kimber are my friends!
Proud to be a U.S. Navy Veteran.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-08-18 : 13:31:00

Create function BadChars (@dat varchar(8000))
returns varchar(8000)
as
begin
declare @BadChr varchar(100), @x int, @y int
set @BadChr = ':,#$%@~'
select @x=1,@y=len(@BadChr)
while @x <= @y
begin
set @dat = replace(@dat, substring(@BadChr,@x,1), '')
set @x=@x+1
end

return @dat
end
go

select 'Hello World', dbo.BadChars(':,#$%@~Hello World')

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2008-08-18 : 14:10:54

Try this:

create table testcheck
(col1 varchar(20) check (col1 not like '%[:\|/?]%'))
go

insert into testcheck values ('hello')
insert into testcheck values ('hello?')

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-08-18 : 14:12:30

quote:
Originally posted by visakh16

Is nt it better to do this validation at client side using some javascript functions before you pass the character values to db for saving?

Absolutely not. This is a data rule, and should not be implemented on the client side unless you want corrupted data entering your database.
The rule can be duplicated at the client side, as good client apps should verify data before sending it to the database and not rely on error messages to do their data checking. But the ultimate gatekeeper of what goes into the database should be the databases, not a client application.

Boycott Beijing Olympics 2008

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-08-18 : 14:18:15

A UDF seems like overkill for this. Why not just implement a constraint such as this:
not like '%[:#$%@~]%'

Boycott Beijing Olympics 2008

pootle_flump

1064 Posts

Posted - 2008-08-19 : 05:25:41

Are the characters you outlined examples or a comprehensive list? If the former: http://weblogs.sqlteam.com/jeffs/archive/2007/04/05/check-constraints.aspx

JackOfAllTrades
Starting Member

12 Posts

Posted - 2008-08-20 : 16:05:33

This list is not complete, but a good example. I was trying the 'not like' constraint but couldn't either get syntax correct for the result I was looking for. I'm away from the office for a few days but when I return I will work with this again. "not like '%[:#$%@~]%'" is the concept I want. Thank you. I'll let you know...

Colt's, Ruger's, Dan Wesson, & Kimber are my friends!
Proud to be a U.S. Navy Veteran.

JackOfAllTrades
Starting Member

12 Posts

Posted - 2008-08-21 : 11:14:27

Someone also suggested using a trigger. Characters I select could be replaced with any string I wanted. Your thoughts?

Colt's, Ruger's, Dan Wesson, & Kimber are my friends!
Proud to be a U.S. Navy Veteran.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-08-21 : 13:57:42

Make sure of your business logic if you are going to have a trigger automatically change data as it is entered.

Boycott Beijing Olympics 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources