Home » SQL & PL/SQL » SQL & PL/SQL » simple sql (oracle 10g)
icon4.gif  simple sql [message #442367] Sun, 07 February 2010 08:03 Go to next message
manishsahu_21
Messages: 25
Registered: February 2010
Location: new delhi
Junior Member

hi my dear
i want to pick only alphanumeric rows that may be contain all special characters like !@#$%^&*()_+":{}|?><>... any things like this and ignore special charcters, so haw can do this pls help.

i use this query

select * from tab1
where regexp_like(col_name,[:alphanum:],'i')

but not extarct rows without of speial charcters.
Re: simple sql [message #442375 is a reply to message #442367] Sun, 07 February 2010 08:30 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select * from tab1 where regexp_like(col_name,([:alnum:]|[:punct:])

or
select * from tab1 where regexp_like(col_name,[:print:])

depending on what you name a special character.

'i' is useless as you include all alphabetical characters.

Regards
Michel

[Updated on: Sun, 07 February 2010 10:33]

Report message to a moderator

Re: simple sql [message #442387 is a reply to message #442375] Sun, 07 February 2010 10:28 Go to previous messageGo to next message
manishsahu_21
Messages: 25
Registered: February 2010
Location: new delhi
Junior Member

hi michel,
thanks for suggestion ,bt these are not correct command
,i want to pick only alpahnumeric values from coloumn as

insert into emp(empno,ename) values(1000,'manish12!@');
insert into emp(empno,ename) values(1001,'manish!@5764');
insert into emp(empno,ename) values(1002,'manish!@764');
insert into emp(empno,ename) values(1003,'manish!@%&(?');
insert into emp(empno,ename) values(1004,'manish!@:');
insert into emp(empno,ename) values(1004,'manish23');
insert into emp(empno,ename) values(1004,'manish')
....
....
select ename from emp
where regexp_like(ename,'[:alnum:]','i');---not done


select ename from emp
where regexp_like(ename,'[:alnum:]|[:punc:]')--as sugesst by u --not done

select ename from emp
where regexp_like(ename,'[:print:]')--not done

syntactically these are correct not any error while we run.
but i desired to get show

ename
-----
manish23
manish

how to get these pls help
Re: simple sql [message #442389 is a reply to message #442387] Sun, 07 February 2010 10:42 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i want to pick only alphanumeric rows that may be contain all special characters like !@#$%^&*()_+":{}|?><>... any things like this and ignore special charcters

I understood this as rows that contain alphanumeric and maybe this kind of special characters and ignore other special characters, now with your example I see this is not what you meant.

SQL> select ename from emp2 where regexp_like(ename,'^([a-z]|[0-9])+$','i');
ENAME
--------------------------------------------------
manish23
manish

2 rows selected.

Regards
Michel

Re: simple sql [message #442390 is a reply to message #442389] Sun, 07 February 2010 10:45 Go to previous message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or better:
SQL> select ename from emp2 where not regexp_like(ename,'[^[:alnum:]]');
ENAME
--------------------------------------------------
manish23
manish

2 rows selected.

Exclude the rows that contains something that is not an alphanumeric character.

Regards
Michel
Previous Topic: which function or code can replace joins
Next Topic: Finding duplicated tables
Goto Forum:
  


Current Time: Sat Sep 24 23:09:35 CDT 2016

Total time taken to generate the page: 0.11516 seconds