Using variable in regualr expression

[EdRev]

I have a condition on my procedure:

if regexp_like(str1,'^L[0-9]{4}') then
str2 := substr(str1,2,length(str1) - 1);
else
str2 := str1;
end if;

I am trying to evaluate str1 to see if the 2nd character to the last character are all digits

I tried the following code to no avail:

if regexp_like(str1,'^L[0-9]{'length(str1) - 1'}') then

if regexp_like(str1,'^L[0-9]{length(str1) - 1}') then

if regexp_like(str1,'^L[0-9]{'||length(str1) - 1||'}') then

Any help will be greatly appreciated.

 

[Turkbear]

Hi,
Try to convert the substr to a number - if it fails, you know one or more are not numbers..

On Error ( do something if it fails) GoTo ....

If To_Number(substr(str1,2)) > 0 then ....






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Dagon]

Try this:

begin
  if regexp_like('&var', '^L[0-9]*$') then
     dbms_output.put_line('yes');
  else
     dbms_Output.put_line('no');
  end if;
end;

Retired (not by choice) Oracle contractor.

 

[EdRev]

Thanks.
I confirmed that length(str1) - 1 is a number.

It is the evaluation code that I am getting syntax error with ot not evaluating at all.

if regexp_like(str1,'^L[0-9]{'length(str1) - 1'}') then

if regexp_like(str1,'^L[0-9]{length(str1) - 1}') then

if regexp_like(str1,'^L[0-9]{'||length(str1) - 1||'}') then

 

[Dagon]

It's not evaluating because it's a load of rubbish. You can't include SQL functions in regular expressions. Why are you trying to do it that way ?

Retired (not by choice) Oracle contractor.

 

[jaxtell]

This works for me

declare 
str1 varchar2(4000) := 'L123455';
len1 number := length(str1) -1;
begin
  if regexp_like(str1, '^L[0-9]{'||len1||'}$') then
     dbms_output.put_line('yes');
  else
     dbms_Output.put_line('no');
  end if;
end;

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[EdRev]

Thank you all.

Dagon's work for me.

Jaxtell, the condition did not work for me. It is similar to my 3rd condition that I tested:

if regexp_like(str1,'^L[0-9]{'||length(str1) - 1||'}') then

 

[Dagon]

I think Jaxtell's version does work. You were missing the crucial $ at the end of your expression. However, there is no need to calculate the length as ^..$ automatically indicates that you want to test the full string.

Retired (not by choice) Oracle contractor.