Home » SQL & PL/SQL » SQL & PL/SQL » do not accept punctuation character while inserting (Oracle 11g)
do not accept punctuation character while inserting [message #589810] Thu, 11 July 2013 01:26 Go to next message
ashwani0301
Messages: 44
Registered: March 2013
Location: Karnatka, Bangalore
Member
Hi All,

I am learning Regular expression I am solving one problem with regular expression.

I am creating a table while inserting I have to restrict puntuation character.

create table emp 
( 
  ename varchar2(30) , 
  constraint ck_ename check ( regexp_like (ename , '[^[:punct:]]')));


and also how I can remove douoble space.
Thanks
Re: do not accept punctuation character while inserting [message #589811 is a reply to message #589810] Thu, 11 July 2013 01:31 Go to previous messageGo to next message
Littlefoot
Messages: 19536
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Set constraint to
check ( NOT regexp_like (ename , '[^[:punct:]]')));

When do you want to remove double space? For existing data (hint: UPDATE statement)? Newly inserted (hint: database trigger)?
Re: do not accept punctuation character while inserting [message #589813 is a reply to message #589810] Thu, 11 July 2013 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ It is more "not regexp_like (ename , '[[:punct:]]')":
SQL> create table t (val varchar2(10));

Table created.

SQL> insert into t values ('aaa');

1 row created.

SQL> insert into t values (',,,');

1 row created.

SQL> insert into t values ('a,a');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t where regexp_like (val, '[^[:punct:]]');
VAL
----------
aaa
a,a

2 rows selected.

SQL> select * from t where not regexp_like (val, '[[:punct:]]');
VAL
----------
aaa

1 row selected.


2/ "how I can remove douoble space"
Is this remove or not allow (constraint)?

Regards
Michel

[Updated on: Thu, 11 July 2013 01:35]

Report message to a moderator

Re: do not accept punctuation character while inserting [message #589815 is a reply to message #589811] Thu, 11 July 2013 01:36 Go to previous messageGo to next message
ashwani0301
Messages: 44
Registered: March 2013
Location: Karnatka, Bangalore
Member
Thanks Michel and LittleFoot.

[Updated on: Thu, 11 July 2013 01:38]

Report message to a moderator

Re: do not accept punctuation character while inserting [message #589816 is a reply to message #589815] Thu, 11 July 2013 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It was a typo, I fix my post when you were posted (note that the example was correct).

Regards
Michel
Re: do not accept punctuation character while inserting [message #589818 is a reply to message #589816] Thu, 11 July 2013 01:48 Go to previous messageGo to next message
ashwani0301
Messages: 44
Registered: March 2013
Location: Karnatka, Bangalore
Member
Hi Michel,

What is the different between these two statement ...

SQL> select * from t where regexp_like (val, '[^[:punct:]]');
VAL
----------
aaa
a,a

2 rows selected.

SQL> select * from t where not regexp_like (val, '[[:punct:]]');
VAL
----------
aaa




what is differnt between using ^ and NOT operator ?

Thanks
Re: do not accept punctuation character while inserting [message #589824 is a reply to message #589818] Thu, 11 July 2013 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The first one select all rows containing at least one character that is not a punctuation one.
The second one select all rows that do NOT contain a punctuation character.

Regards
Michel
Re: do not accept punctuation character while inserting [message #589826 is a reply to message #589824] Thu, 11 July 2013 02:55 Go to previous messageGo to next message
ashwani0301
Messages: 44
Registered: March 2013
Location: Karnatka, Bangalore
Member
Thank you very much Michel.
Re: do not accept punctuation character while inserting [message #589892 is a reply to message #589818] Thu, 11 July 2013 12:44 Go to previous message
Solomon Yakobson
Messages: 2010
Registered: January 2010
Senior Member
If you want to use [^[:punct:]] you must ensure all characters in ename are [^[:punct:]]:

constraint ck_ename check(regexp_like(ename,'^[^[:punct:]]*$'))


SY.
Previous Topic: Oracle DB 11g "LEVEL" issue
Next Topic: Different between varchar2(n bytes) and varchar2(n char)?
Goto Forum:
  


Current Time: Mon Sep 01 16:39:43 CDT 2014

Total time taken to generate the page: 0.16376 seconds