Home » SQL & PL/SQL » SQL & PL/SQL » not to allow Special character thru insert statement (oracle10g, 10.2.0.2.0, AIX52)
not to allow Special character thru insert statement [message #423609] Fri, 25 September 2009 09:30 Go to next message
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.
Re: not to allow Special character thru insert statement [message #423610 is a reply to message #423609] Fri, 25 September 2009 09:36 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Lookup the syntax for Translate and Replace
Re: not to allow Special character thru insert statement [message #423611 is a reply to message #423610] Fri, 25 September 2009 09:37 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
And/Or create a check constraint on the column.
Re: not to allow Special character thru insert statement [message #423623 is a reply to message #423609] Fri, 25 September 2009 10:49 Go to previous messageGo to next message
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...
Re: not to allow Special character thru insert statement [message #423625 is a reply to message #423609] Fri, 25 September 2009 11:06 Go to previous messageGo to next message
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.
Re: not to allow Special character thru insert statement [message #423630 is a reply to message #423625] Fri, 25 September 2009 11:37 Go to previous messageGo to next message
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..
Re: not to allow Special character thru insert statement [message #423631 is a reply to message #423630] Fri, 25 September 2009 12:40 Go to previous messageGo to next message
joy_division
Messages: 4641
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.
Re: not to allow Special character thru insert statement [message #423643 is a reply to message #423611] Fri, 25 September 2009 17:19 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: not to allow Special character thru insert statement [message #424057 is a reply to message #423643] Wed, 30 September 2009 02:24 Go to previous message
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..
Previous Topic: Help me to correct this query
Next Topic: Need Query
Goto Forum:
  


Current Time: Wed Dec 07 08:41:08 CST 2016

Total time taken to generate the page: 0.11455 seconds