| REGEXP [message #603849] |
Thu, 19 December 2013 22:16  |
|
|
Hi,
I want to filter records based on a column, if there is any special characters in it.
Please help.
Example:
col
===
abc123
x<y
x=y
x$ y
x,y
x.y
abc
The desired SQL should filter following records only from above record set:
x<y
x=y
x$ y
x, y
x. y
|
|
|
|
|
|
| Re: REGEXP [message #603851 is a reply to message #603850] |
Thu, 19 December 2013 22:47  |
|
|
Ok. Table name is 'test_tbl' and column name is 'col1'
List of special Characters: ` ~ ! @ # $ % ^ & * ( ) _ - + = { [ } ] | \ : ; ' " , < . > ? /
|
|
|
|
| Re: REGEXP [message #603852 is a reply to message #603851] |
Thu, 19 December 2013 22:59 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Use the following to negate the alpha-numeric character class, so those rows will be picked which are having characters other than alphabets and numbers.
[Edit : fixed a typo]
[Updated on: Thu, 19 December 2013 23:06] Report message to a moderator |
|
|
|
| Re: REGEXP [message #603853 is a reply to message #603852] |
Thu, 19 December 2013 23:18 |
|
|
I have writen following SQL, but it is not serving my purpose:
SELECT * FROM test_tbl
WHERE REGEXP_LIKE (col1,'[^[:alnum:]]');
It is showing below output:
x=y
x$ y
x,y
x.y
x<y
But I want below output:
abc123
abc
My goal is, filter those rows only which contains one or more special characters( ` ~ ! @ # $ % ^ & * ( ) _ - + = { [ } ] | \ : ; ' " , < . > ? /). I don't want to filter those records which containts only characters or only numeric or only characters and numeric mixed without special characters.
|
|
|
|
| Re: REGEXP [message #603854 is a reply to message #603853] |
Thu, 19 December 2013 23:23 |
|
|
I am getting my desired output using below SQL
SELECT * FROM test_tbl
MINUS
SELECT * FROM test_tbl
WHERE REGEXP_LIKE (col1,'[^[:alnum:]]');
Is it possible with single SQL?
|
|
|
|
| Re: REGEXP [message #603858 is a reply to message #603854] |
Fri, 20 December 2013 00:08 |
 |
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
You don't need MINUS set operator at all, it would be enough if you used...
where NOT regexp_like(col, '[^[:alnum:]]');
(Hint: NOT).
Or even...
where regexp_like(col, '^[[:alnum:]]+$'); [Updated on: Fri, 20 December 2013 00:09] Report message to a moderator |
|
|
|
|
|
| Re: REGEXP [message #603861 is a reply to message #603860] |
Fri, 20 December 2013 00:40 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
mokarem wrote on Fri, 20 December 2013 11:58
But following is not
where regexp_like(col, '^:alnum:+$');
SQL> WITH DATA AS(
2 SELECT 'abc123' a from dual UNION ALL
3 SELECT 'x<y' a from dual UNION ALL
4 SELECT 'x=y' a from dual UNION ALL
5 SELECT 'x$ y' a from dual UNION ALL
6 SELECT 'x,y' a from dual UNION ALL
7 SELECT 'x.y' a from dual UNION ALL
8 SELECT 'abc' a from dual)
9 SELECT * FROM DATA
10 WHERE NOT regexp_like(a,'[^[:alnum:]]');
A
------
abc123
abc
SQL> WITH DATA AS(
2 SELECT 'abc123' a from dual UNION ALL
3 SELECT 'x<y' a from dual UNION ALL
4 SELECT 'x=y' a from dual UNION ALL
5 SELECT 'x$ y' a from dual UNION ALL
6 SELECT 'x,y' a from dual UNION ALL
7 SELECT 'x.y' a from dual UNION ALL
8 SELECT 'abc' a from dual)
9 SELECT * FROM DATA
10 WHERE regexp_like(a, '^[[:alnum:]]+$');
A
------
abc123
abc
|
|
|
|
| Re: REGEXP [message #603887 is a reply to message #603861] |
Fri, 20 December 2013 03:29 |
|
|
Thanks Lalit Kumar.
In my post where I wrote one is not working, there was typing mistake. Sorry.
Your query is working fine.
WITH TST_TBL as (
select 'abc123' col1 from dual union all
select 'x=y' col1 from dual union all
select 'x$ y' col1 from dual union all
select 'x,y' col1 from dual union all
select 'x.y' col1 from dual union all
select 'abc abc' col1 from dual union all
select 'x<y' col1 from dual union all
select 'x "y"' col1 from dual union all
select 'x ''y hg''' col1 from dual union all
select 'x <= y' col1 from dual
)
select * from tst_tbl
where regexp_like(col1,'^[[:alnum:]]+$')
col1
-----
abc123
But I am getting one more problem which I didn't discussed. I want to see below two records
col1
----
abc123
abc abc
If there is space in between text that shouldn't be ignored.I can do this as below:
WITH TST_TBL as (
select 'abc123' col1 from dual union all
select 'x=y' col1 from dual union all
select 'x$ y' col1 from dual union all
select 'x,y' col1 from dual union all
select 'x.y' col1 from dual union all
select 'abc abc' col1 from dual union all
select 'x<y' col1 from dual union all
select 'x "y"' col1 from dual union all
select 'x ''y hg''' col1 from dual union all
select 'x <= y' col1 from dual
)
select * from tst_tbl
where regexp_like(regexp_replace(col1,'[[:space:]]'),'^[[:alnum:]]+$')
col1
---
abc123
abc abc
Is there any other way to do the same using REGEXP?
[Updated on: Fri, 20 December 2013 03:32] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: REGEXP [message #604088 is a reply to message #603944] |
Mon, 23 December 2013 23:40 |
|
|
Sharing with you after solving the problem and modifying the solution. Simply I can achive my purpose using below:
SELECT *
FROM emp
WHERE REGEXP_LIKE(ename,'[[:punct:]]');
|
|
|
|
|
|
| Re: REGEXP [message #604106 is a reply to message #604088] |
Tue, 24 December 2013 02:18 |
|
|
Sorry to confuse you about my requirements.
I requied to filter those records, where in a text field if there is any special characters( ` ~ ! @ # $ % ^ & * ( ) _ - + = { [ } ] | \ : ; ' " , < . > ? /). The field can have characters, digits (0-9) and spaces only.
Discussing with you people I resolve this issue using below
select * from md_quality_process
where regexp_like(quality_process_desc,'[^[:alnum:] ]');
Today I modified this and getting same output using below
select * from md_quality_process
where regexp_like(quality_process_desc,'[[:punct:]]');
From existing records, both query are giving same output.
|
|
|
|
| Re: REGEXP [message #604128 is a reply to message #604106] |
Tue, 24 December 2013 05:55 |
|
|
Now I need to replace if there are adjucent multiple blank spaces with single blank space. If there are any line break, I need to replace it with single blank space.
I can do these in two speparate SQL.
To remove multiple spaces, I can use below SQL:
SELECT REGEXP_REPLACE(' There should not be multiple blank spaces.','( ){2,}',' ')
FROM DUAL;
To remove line break, I can use below SQL:
SELECT REGEXP_REPLACE(' There should not be
multiple blank spaces.',CHR(10),' ')
FROM DUAL;
How can I achieve this from single SQL Statement?
|
|
|
|
|
|
| Re: REGEXP [message #604140 is a reply to message #604128] |
Tue, 24 December 2013 10:06 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
mokarem wrote on Tue, 24 December 2013 06:55Now I need to replace if there are adjucent multiple blank spaces with single blank space. If there are any line break, I need to replace it with single blank space.
Assuming you don't need to remore leading/trailing spaces (just to replace leading/trailing spaces with a single space):
SELECT '[' || REGEXP_REPLACE(' There should not be
multiple blank spaces. ','(\s|' || CHR(10) || ')+',' ') || ']'
FROM DUAL
/
'['||REGEXP_REPLACE('THERESHOULDNOTBEMULTIPLEB
----------------------------------------------
[ There should not be multiple blank spaces. ]
SY.
P.S. I wrapped regexp with brackets just to show that single leading/trailing space.
|
|
|
|
| Re: REGEXP [message #604185 is a reply to message #604140] |
Wed, 25 December 2013 21:52 |
|
|
Thanks Solomon. It is working.
Followoing also remove the leading and trailing spaces
SELECT REGEXP_REPLACE(' There should not
be multiple blank spaces. ','( ){2,}|'||chr(10),' ')
FROM DUAL;
'\s' is simple
|
|
|
|
| Re: REGEXP [message #604221 is a reply to message #604185] |
Thu, 26 December 2013 07:08 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
mokarem wrote on Wed, 25 December 2013 22:52Followoing also remove the leading and trailing spaces
No, it doesn't:
SQL> SELECT '[' || REGEXP_REPLACE(' There should not
2 be multiple blank spaces. ','( ){2,}|'||chr(10),' ') || ']'
3 FROM DUAL
4 /
'['||REGEXP_REPLACE('THERESHOULDNOTBEMULTIPLEB
----------------------------------------------
[ There should not be multiple blank spaces. ]
SQL>
In fact, it will leave multiple spaces if there are multiple new line characters in a row, while my suggestion doesn't:
SQL> SELECT '[' || REGEXP_REPLACE(' There should not
2
3
4
5
6 be multiple blank spaces. ','( ){2,}|'||chr(10),' ') || ']'
7 FROM DUAL
8 /
'['||REGEXP_REPLACE('THERESHOULDNOTBEMULTIPLEBLANK
--------------------------------------------------
[ There should not be multiple blank spaces. ]
SQL> SELECT '[' || REGEXP_REPLACE(' There should not be
2
3
4
5 multiple blank spaces. ','(\s|' || CHR(10) || ')+',' ') || ']'
6 FROM DUAL
7 /
'['||REGEXP_REPLACE('THERESHOULDNOTBEMULTIPLEB
----------------------------------------------
[ There should not be multiple blank spaces. ]
SQL>
Same way it will not work if there is a single space followed by new line, while my suggestion works OK:
SQL> SELECT '[' || REGEXP_REPLACE(' There should not ' || '
2 be multiple blank spaces. ','( ){2,}|'||chr(10),' ') || ']'
3 FROM DUAL
4 /
'['||REGEXP_REPLACE('THERESHOULDNOT'||'BEMULTIP
-----------------------------------------------
[ There should not be multiple blank spaces. ]
SQL> SELECT '[' || REGEXP_REPLACE(' There should not ' || '
2 be multiple blank spaces. ','(\s|' || CHR(10) || ')+',' ') || ']'
3 FROM DUAL
4 /
'['||REGEXP_REPLACE('THERESHOULDNOT'||'BEMULTI
----------------------------------------------
[ There should not be multiple blank spaces. ]
SQL>
SY.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
