OraFAQ Forum: SQL & PL/SQL » not to allow Special character thru insert statement

Home » SQL & PL/SQL » SQL & PL/SQL » not to allow Special character thru insert statement (oracle10g, 10.2.0.2.0, AIX52)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

not to allow Special character thru insert statement [message #423609]

Fri, 25 September 2009 09:30

chandu_imax
Messages: 46
Registered: May 2008
Location: Hyderabad

Member

Hi All,

I want an insert statement that do not allow the special characters as it having the valid values like : A-Z,0-9.

Insert Query is :

insert into XYZ values (COL1, COL2, COL3);

Not to allow the special characters for COL1 column.

Please help me in this regard.

--- Thanks in Advance.

Report message to a moderator

Re: not to allow Special character thru insert statement [message #423610 is a reply to message #423609]

Fri, 25 September 2009 09:36

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Lookup the syntax for Translate and Replace

Report message to a moderator

Re: not to allow Special character thru insert statement [message #423611 is a reply to message #423610]

Fri, 25 September 2009 09:37

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

And/Or create a check constraint on the column.

Report message to a moderator

Re: not to allow Special character thru insert statement [message #423623 is a reply to message #423609]

Fri, 25 September 2009 10:49

rajesh4851
Messages: 89
Registered: January 2007

Member

what kind of special characters you are trying to insert? what is the data type of the COL1. You can convert into ASCII while inserting...

Report message to a moderator

Re: not to allow Special character thru insert statement [message #423625 is a reply to message #423609]

Fri, 25 September 2009 11:06

chandu_imax
Messages: 46
Registered: May 2008
Location: Hyderabad

Member

Hi,

Datatype is char(1) and I want to insert # or $ or % etc.. But it should not allow me to do so. It should allow only 1-9 and A-Z.

Thanks.

Report message to a moderator

Re: not to allow Special character thru insert statement [message #423630 is a reply to message #423625]

Fri, 25 September 2009 11:37

rajesh4851
Messages: 89
Registered: January 2007

Member

Hi Chandu,
I have tried to insert such special characters in my test table. i am able to do it. i am using 8i.

In your case , special characters might be occupying more than one byte. That is the reason, it is thorowing error. What is the exact error ? Give more details so that we can give you some good idea..

Report message to a moderator

Re: not to allow Special character thru insert statement [message #423631 is a reply to message #423630]

Fri, 25 September 2009 12:40

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

I think you have misread the question. I see at as user wants to PREVENT special character from being inserted.
A check constraint as has been mentioned is what needs to be done.

Report message to a moderator

Re: not to allow Special character thru insert statement [message #423643 is a reply to message #423611]

Fri, 25 September 2009 17:19

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

ThomasG wrote on Sat, 26 September 2009 00:37

And/Or create a check constraint on the column.

And there is your answer.

For CHAR(1), you could probably just get away with

COL BETWEEN '0' and '9' OR COL BETWEEN 'A' AND 'Z'

Ross Leishman

Report message to a moderator

Re: not to allow Special character thru insert statement [message #424057 is a reply to message #423643]

Wed, 30 September 2009 02:24

chandu_imax
Messages: 46
Registered: May 2008
Location: Hyderabad

Member

Hi All,

Thanks for your support. I got the Solution. I had added below constraint to the table.

Alter table test123
add CONSTRAINT check_col1
CHECK ((col1 between 'A' and 'Z')or(col1 between '0' and '9' ));

Regards,
Chandu..

Report message to a moderator

Previous Topic:	Help me to correct this query
Next Topic:	Need Query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 16:09:23 CST 2024