| REGEXP_LIKE [message #560111] |
Tue, 10 July 2012 11:48  |
 |
sulabhagra
Messages: 56 Registered: April 2012
|
Member |
|
|
Hello,
I am trying to use regular expressions within my code to check if the field is a telephone no or not. For that I need to check if there is a '+' in the beginning and also possible spaces which I need to ignore and consider nos otherwise only.
Now, while testing this, it is coming right for both the correct and the wrong cases and am not sure what I am doing is wrong.
select
case when regexp_like('78+9','^\+{0,1}') then 1 else 0 end
from dual
also satisfies the condition and '+789' also .. so am not sure how to check if it is correct, can you please help.
In essensce I need to ignore first '+' and spaces in middle if any and the rest should be nos only.
Thanks a lot,
S
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: REGEXP_LIKE [message #560267 is a reply to message #560263] |
Wed, 11 July 2012 10:29   |
 |
Michel Cadot
Messages: 54223 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
1/ what I meant is:
SQL> select
2 case when regexp_like('+','^ *\+{0,1}[[:digit:] ]*$') then 1 else 0 end res
3 from dual;
RES
----------
1
Do you want 1 or 0 for this case?
2/ Currently:
SQL> select
2 case when regexp_like(null, '^ *\+{0,1}[[:digit:] ]*$') then 1 else 0 end res
3 from dual;
RES
----------
0
If you want to accept it then the simplest way is to add a NVL call:
SQL> select
2 case when regexp_like(nvl(null,' '), '^ *\+{0,1}[[:digit:] ]*$') then 1 else 0 end res
3 from dual;
RES
----------
1
Which also shows that the regexp also admits a single blank as string:
SQL> select
2 case when regexp_like(' ', '^ *\+{0,1}[[:digit:] ]*$') then 1 else 0 end res
3 from dual;
RES
----------
1
Regards
Michel
[Edit: typos]
[Updated on: Wed, 11 July 2012 11:46] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
| Re: REGEXP_LIKE [message #560474 is a reply to message #560471] |
Fri, 13 July 2012 07:51   |
Solomon Yakobson
Messages: 1402 Registered: January 2010
|
Senior Member |
|
|
sulabhagra wrote on Fri, 13 July 2012 08:08am I correct in assuming the following interpretation of this RE.
Don't assume - READ documentation. Asterisk is 0 or more occurrences.
{0,1} can be shortened with question mark.
[[:digit:] ]* means digit_or_space repeated 0_or_more_times.
Therefore, null_or_bunch_of_spaces_without_any_digits fall under the pattern. Since
your requirement is to have at least one digit Michel used [[:digit:]] [[:digit:]_]*
which means digit followed by any number of_digits and/or spaces thus enforcing at
least one digit. Btw, first [[:digit :]] can be shortened with \d and [[:digit:] ]
with [0-9 ].
SY.
[Updated on: Sat, 14 July 2012 12:41] by Moderator Report message to a moderator
|
|
|
|
|
|
| Re: REGEXP_LIKE [message #560568 is a reply to message #560567] |
Sat, 14 July 2012 11:53   |
Solomon Yakobson
Messages: 1402 Registered: January 2010
|
Senior Member |
|
|
Look at your pattern. It says string starts with 0 or more spaces foloowed by optional sign (+ or minus), followed by one digit, followed by optional dot followed by 0 or more digits. Since all values except 0 do not have a digit in front of the dot they do not conform to your pattern.
SY.
|
|
|
|
|
|
|
|
|
|
| Re: REGEXP_LIKE [message #561081 is a reply to message #561074] |
Wed, 18 July 2012 13:49   |
Solomon Yakobson
Messages: 1402 Registered: January 2010
|
Senior Member |
|
|
You need to describe rules in words. Based on data sample:
with data as (
select '020-7332-6474' as val from dual
union all
select '44(0)8709507900' from dual
union all
select '0033 (0) 240899432' from dual
union all
select '44 (0) 1483 452 622' from dual
union all
select '1(604) 592-2908' from dual
union all
select 'MOB (604) 592-2908' from dual
union all
select '+1(604)-592-2908' from dual
union all
select '06045922908' from dual
)
select ''''||val||'''' val,
case
when regexp_like(nvl(replace(val,' '),'0'),'^\+?\d*(\(\d+\))?(-?\d+)+$') then 1
else 0
end res
from data
/
VAL RES
--------------------- ----------
'020-7332-6474' 1
'44(0)8709507900' 1
'0033 (0) 240899432' 1
'44 (0) 1483 452 622' 1
'1(604) 592-2908' 1
'MOB (604) 592-2908' 0
'+1(604)-592-2908' 1
'06045922908' 1
8 rows selected.
SQL>
SY.
|
|
|
|
|
|
| Re: REGEXP_LIKE [message #561111 is a reply to message #561089] |
Thu, 19 July 2012 00:40  |
 |
Michel Cadot
Messages: 54223 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Note (for your futur posts) that "valid tel nos" has a different meaning in each country, this is why you have to clearly define what is valid.
Regards
Michel
|
|
|
|