Home » SQL & PL/SQL » SQL & PL/SQL » REGEXP_LIKE
REGEXP_LIKE [message #560111] Tue, 10 July 2012 11:48 Go to next message
sulabhagra
Messages: 57
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 #560117 is a reply to message #560111] Tue, 10 July 2012 12:20 Go to previous messageGo to next message
Michel Cadot
Messages: 58520
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select 
case when regexp_like('78+9','^ *\+{0,1}[[:digit:] ]*$') then 1 else 0 end res
from dual;

Regards
Michel
Re: REGEXP_LIKE [message #560120 is a reply to message #560117] Tue, 10 July 2012 12:29 Go to previous messageGo to next message
sulabhagra
Messages: 57
Registered: April 2012
Member
Thanks Michel, How can I ignore the space in between, as there are values like 0112 548 2135 which is a valid no.

Thanks,
S
Re: REGEXP_LIKE [message #560124 is a reply to message #560120] Tue, 10 July 2012 13:12 Go to previous messageGo to next message
Michel Cadot
Messages: 58520
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you try my regexp with your last example?
Do you understand my regexp?

Regards
Michel
Re: REGEXP_LIKE [message #560137 is a reply to message #560124] Tue, 10 July 2012 14:35 Go to previous messageGo to next message
sulabhagra
Messages: 57
Registered: April 2012
Member
I understand it now, my bad, I copied the piece in my code and skipped the 'space' by mistkae.
Now it is all clear and working fine. Many thanks for all the help.

Regards,
S
Re: REGEXP_LIKE [message #560165 is a reply to message #560137] Wed, 11 July 2012 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 58520
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Note that this regexp accept the string "+" alone, if you don't want this you have to slighty modify it.
I let you do it as exercise and post the solution; if you can't do not hesitate to ask.

Regards
Michel
Re: REGEXP_LIKE [message #560263 is a reply to message #560165] Wed, 11 July 2012 10:20 Go to previous messageGo to next message
sulabhagra
Messages: 57
Registered: April 2012
Member
Hi Michel,

I understand the part of the RE which checks for the single or none plus signs is \+{0,1} and there is no need to change this as it is perfect for the need.

Now, what I need to ask you is that while checking for the expression it fails the NULL fields as well, do we need to add additional condition to cater to NULL or we can tweak this as well.


Thanks a lot,
Sulabh
Re: REGEXP_LIKE [message #560267 is a reply to message #560263] Wed, 11 July 2012 10:29 Go to previous messageGo to next message
Michel Cadot
Messages: 58520
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 #560268 is a reply to message #560267] Wed, 11 July 2012 10:37 Go to previous messageGo to next message
sulabhagra
Messages: 57
Registered: April 2012
Member
Right, got your point, we did not had that value in the data so did not think about it, and yes It will be good to remove this single '+' as well or '-' in case that is present.

Can you please suggest how to show them as fail(0) as well.

Thanks,
S
Re: REGEXP_LIKE [message #560279 is a reply to message #560268] Wed, 11 July 2012 12:06 Go to previous messageGo to next message
Michel Cadot
Messages: 58520
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with 
  2    data as (
  3      select '78+9' val from dual
  4      union all
  5      select '+789' from dual
  6      union all
  7      select '- 789' from dual
  8      union all
  9      select '0112 548 2135 ' from dual
 10      union all
 11      select '+' from dual
 12      union all
 13      select '-' from dual
 14      union all
 15      select null from dual
 16      union all
 17      select ' ' from dual
 18    )
 19  select '"'||val||'"' val, 
 20         case 
 21           when regexp_like(nvl(trim(val),'0'), '^ *[-\+]{0,1} *[[:digit:]][[:digit:] ]*$') 
 22             then 1 
 23           else 0 
 24         end res
 25  from data
 26  /
VAL                     RES
---------------- ----------
"78+9"                    0
"+789"                    1
"- 789"                   1
"0112 548 2135 "          1
"+"                       0
"-"                       0
""                        1
" "                       1

Regards
Michel
Re: REGEXP_LIKE [message #560471 is a reply to message #560279] Fri, 13 July 2012 07:08 Go to previous messageGo to next message
sulabhagra
Messages: 57
Registered: April 2012
Member
Thanks a lot Michel, this was a big help. Now to interpret this RE well, am I correct in assuming the following interpretation of this RE.

^ - Start
" *" - One or more Space
"[-\+]{0,1}" - Zero one One (+-) sign
" *" - Why is this second space used for?
":digit:[[:digit:] ]*" - What benefit is there of having double posix usage ??
"$'" - End of String

Also one last question, if I have to test for a string having both a String and No. in it as a random combination and no special chars, what is the way we handle that? As I tried these together ":digit::alpha:" and it was not working.

Many thanks once again.
Sulabh
Re: REGEXP_LIKE [message #560472 is a reply to message #560471] Fri, 13 July 2012 07:11 Go to previous messageGo to next message
sulabhagra
Messages: 57
Registered: April 2012
Member
[[: digit:] ] [[:alpha:] ]
got somehow replaced in the previous response.
Re: REGEXP_LIKE [message #560474 is a reply to message #560471] Fri, 13 July 2012 07:51 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1950
Registered: January 2010
Senior Member
sulabhagra wrote on Fri, 13 July 2012 08:08
am 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 #560567 is a reply to message #560474] Sat, 14 July 2012 11:13 Go to previous messageGo to next message
sulabhagra
Messages: 57
Registered: April 2012
Member
Thanks a lot Solomon, I did read it but the more I practice it with other examples it fails and confuses me. E.g If I have numeric fields and use the following RE it fails.


with 
    data as (
     select 0.0027 as val  from dual
      union all
      select 0.0068563 from dual
      union all
      select 0.007685 from dual
      union all
      select 0 from dual
    )
  select '"'||val||'"' val, 
         case 
           when regexp_like(nvl(trim(val),0), '^ *[-\+]{0,1}[[:digit:]]\.?[[:digit:]]*$') 
             then 1 
           else 0 
         end res
  from data



Any idea what I am doing is wrong, as I am not sure what is the problem.

Thanks a lot,
S
Re: REGEXP_LIKE [message #560568 is a reply to message #560567] Sat, 14 July 2012 11:53 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1950
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 #560581 is a reply to message #560568] Sun, 15 July 2012 03:25 Go to previous messageGo to next message
sulabhagra
Messages: 57
Registered: April 2012
Member
Thanks a lot Solomon, now it is a lot more clearer than what I understood of it before. Thanks for all the explanation and patience.

Regards,
S
Re: REGEXP_LIKE [message #561074 is a reply to message #560111] Wed, 18 July 2012 11:59 Go to previous messageGo to next message
sulabhagra
Messages: 57
Registered: April 2012
Member
Hi Solomon/Michel,

I am not sure what, but there is something wrong with the below expression which I am not able to understand as all the below examples seem to fail, whereas based on my considerations only one of them having aplhanumeric data should fail.


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(trim(val),'0'), '^\+?[[:digit:]]*[[\(\.-] ]?[:digit:]*[\)\.-] ]?[:digit:]*$') 
       then 1 
       else 0 
end res 
from data


Can you please help me with this, hoping this time I understand it well not to bother you guys again.

Thanks in advance,
S
Re: REGEXP_LIKE [message #561077 is a reply to message #561074] Wed, 18 July 2012 13:04 Go to previous messageGo to next message
Michel Cadot
Messages: 58520
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does your regexp intend to find?

Regards
Michel

[Updated on: Wed, 18 July 2012 13:08]

Report message to a moderator

Re: REGEXP_LIKE [message #561081 is a reply to message #561074] Wed, 18 July 2012 13:49 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1950
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 #561089 is a reply to message #561081] Wed, 18 July 2012 14:29 Go to previous messageGo to next message
sulabhagra
Messages: 57
Registered: April 2012
Member
Thanks a lot, I will deep dive into it tomorrow to understand what I did wrong when I have Oracle to play with. But all I was doing as correctly guessed by Solomon was to parse all the valid tel nos, and flag any string or in appropriate data in it.

Thanks once again!
S
Re: REGEXP_LIKE [message #561111 is a reply to message #561089] Thu, 19 July 2012 00:40 Go to previous message
Michel Cadot
Messages: 58520
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
Previous Topic: PL/SQL Issue
Next Topic: Howto execute several sql statements in one command of the DataAdapter Class
Goto Forum:
  


Current Time: Wed Jul 23 15:38:11 CDT 2014

Total time taken to generate the page: 0.09427 seconds