1 2 3 4 47 Replies Latest reply on Jan 10, 2014 7:10 PM by Hoek Go to original post
• ###### 15. Re: REGEXP_LIKE, oh where for art thou?

Hi Greg,

I think i understand what you said when you told me to put the "regexp's" in a table of sorts, and I will try that also, and see if I can incorporate that into the other program "Head Spins" (*)

What a wonderful day this is:

Thank you, Boneist, Greg, Frank and Hoek for helping me out today.

• ###### 16. Re: REGEXP_LIKE, oh where for art thou?

Greg, it's Xev,

I worked out all the patterns except the very last 2.

I cannot seem to wrap my mind around these 2 formats.

TID 777777777                                      3 alpha, space nine digits

K77-77-7777                                        1 alpha 2 dig - 2 dig - 4 dig

I have the rest of them right here:

with w_regexp as (

select '^([[:alpha:]]|[[:alpha:]]{3})[0-9]{6}\$'             regexp from dual union all  -- 6 numerics

select '^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?\$'  from dual union all  -- 8 numerics

select '^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{9}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?\$'  from dual union all  -- 9 numerics

select '^([[:alpha:]]|[[:alpha:]]{3})[0-9]{7}\$'             regexp from dual union all  -- 7 numerics

select '^([[:alpha:]]|[[:alpha:]]{3})[0-9]{12}\$'            regexp from dual union all  -- 12 numerics

select '^([[:alpha:]]|[[:alpha:]]{3})[0-9]{11}\$'            regexp from dual union all  -- 11 numerics

select '^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{13}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?\$'  from dual union all  -- 13 numerics

select '^([[:alpha:]]|[0-9])([0-9]{2}-){2}[0-9]{4}\$'               from dual            -- hyphens

)

select *

from fnd_patterns a,

w_regexp b

where regexp_like ( a.testcol, b.regexp )

order by testcol desc

/

• ###### 17. Re: REGEXP_LIKE, oh where for art thou?

I just figured out one of them

'^[[:alpha:]]{3}\s[0-9]{9}\$' = for TID 777777777

Now, I got to figure out the very last one...

z77-77-7777                                        1 alpha 2 dig - 2 dig - 4 dig

• ###### 18. Re: REGEXP_LIKE, oh where for art thou?

hint:

there isn't much difference between:

777-77-7777

and

z77-77-7777

Both should be caught by this guy:

select '^([[:alpha:]]|[0-9])([0-9]{2}-){2}[0-9]{4}\$'               from dual            -- hyphens

which breaks down to:

^                                -- beginning of line

([[:alpha:]]|[0-9])              -- 1 alpha OR 1 numeric.

([0-9]{2}-){2}                   -- ( 2 numerics AND hyphen ) ... twice

[0-9]{4}                         -- 4 numerics

\$                                -- end of line.

• ###### 19. Re: REGEXP_LIKE, oh where for art thou?

You know what Greg, I think in second place is a O, rather than a number....Geez...

It's format is XO7-77-7777, so 2 alpha, then 1 digit, dash 2 digits, then dash 4 digits...

So how do I tell it that it's first 2 are alpha,followed by a digits right before the first dash?

Does this: ^([[:alpha:]]|[[:alpha:]] mean that it's starts with alpha twice??

• ###### 20. Re: REGEXP_LIKE, oh where for art thou?

ok, so just adjust it for that ...

so:

777-77-7777

and

XO7-77-7777

only those two?

then just adjust this accordingly:

^                                -- beginning of line

([[:alpha:]]{2}|[0-9]{2})        -- 2 alpha OR 2 numeric.

([0-9]-)                         -- 1 numeric and hyphen

([0-9]{2}-)                      -- ( 2 numerics AND hyphen )

[0-9]{4}                         -- 4 numerics

\$                                -- end of line.

*Shrug*

That's the nice part about keeping the regexp's a little simpler .. easier to read/edit/maintain them if something subtle changes later

(Just because you CAN do something, doesn't mean you SHOULD do that thing .. )

Reminds me of this:

http://blog.wolfram.com/2011/12/01/the-2011-mathematica-one-liner-competition/

Fun little exercise, but not always useful or practical in real life

• ###### 21. Re: REGEXP_LIKE, oh where for art thou?

Ohhh, this helped me tremdenously!!!! When you explained this to me just now

This break-down is just what I needed

^                                -- beginning of line

([[:alpha:]]{2}|[0-9]{2})        -- 2 alpha OR 2 numeric.

([0-9]-)                         -- 1 numeric and hyphen

([0-9]{2}-)                      -- ( 2 numerics AND hyphen )

[0-9]{4}                         -- 4 numerics

\$                                -- end of line.

Big Hugs

• ###### 22. Re: REGEXP_LIKE, oh where for art thou?

No problem ..

and as a standing "challenge" to those regexp "gurus" out there

(Just because you CAN do something, doesn't mean you SHOULD do that thing .. )

I'd still love to see all that logic squashed into a single, minimal regexp - if possible lol Would be interesting never-the-less lol

• ###### 23. Re: REGEXP_LIKE, oh where for art thou?

It worked like a charm

select *

from fnd_pattern

where regexp_like(testcol, '^([[:alpha:]]{2}|[0-9]{2})([0-9]-)([0-9]{2}-)[0-9]{4}\$')

• ###### 24. Re: REGEXP_LIKE, oh where for art thou?

Greg Wrote:

No problem ..

and as a standing "challenge" to those regexp "gurus" out there

(Just because you CAN do something, doesn't mean you SHOULD do that thing .. )

I'd still love to see all that logic squashed into a single, minimal regexp - if possible lol Would be interesting never-the-less lol

Now, what I have to do with all of this is get it to work as right after I loop though a series of tables, then executes immediately, then does a select case using

our ever-loved [Regexp_Like]....I have no idea what this will do to the PGA .....But like you said, I should put all this regexp into a table, then loop through the table, then do the case select?

• ###### 25. Re: REGEXP_LIKE, oh where for art thou?

Xev Bellringer wrote:

.But like you said, I should put all this regexp into a table, then loop through the table,

Woah .. slow down. I never said that

What I said was:

So based on that, I'm thinking it might help to put the regexp's into a "table" of sorts .. would make it easy to maintain and update if you have changes in future.

I mentioned no loop ..

Look at my original example:

with w_regexp as (

select '^([[:alpha:]]|[[:alpha:]]{3})[0-9]{6}\$'            regexp from dual union all  -- 6 numerics

select '^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?\$'  from dual union all  -- 8 numerics

select '^([[:alpha:]]|[0-9])([0-9]{2}-){2}[0-9]{4}\$'              from dual            -- hyphens

)

select *

from fnd_patterns a,

w_regexp b

where regexp_like ( a.testcol, b.regexp )

/

Now imagine you have a TABLE name "w_regexp" ...

with values such as:

REGEXP

------------------------------------------

^([[:alpha:]]|[[:alpha:]]{3})[0-9]{6}\$

^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?\$

....

etc.

Then they "query" becomes just:

select *

from fnd_patterns a,

w_regexp b

where regexp_like ( a.testcol, b.regexp )

/

done ...

simple.  no loop.

just join ...

(and I promise nothing regards to performance on this O_o  depending on your data size, this might run very slow ... be careful ...)

• ###### 26. Re: REGEXP_LIKE, oh where for art thou?

Just for you Greg, This is all the RegExp i ended up with

^([[:alpha:]]|[[:alpha:]]{3})[0-9]{6}\$

^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?\$

^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?\$

^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?\$

^[[:alpha:]]{3}\s[0-9]{9}\$

^([[:alpha:]]|[[:alpha:]]{3})[0-9]{12}\$

^([[:alpha:]]{2}|[0-9]{2})([0-9]-)([0-9]{2}-)[0-9]{4}\$

^([[:alpha:]]|[[:alpha:]]{3})[0-9]{11}\$

^([[:alpha:]]|[[:alpha:]]{3})[0-9]{6}\$

^([[:alpha:]]|[[:alpha:]]{3})[0-9]{7}\$

^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{13}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?\$

^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{9}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?\$

^([[:alpha:]]{2}|[0-9]{2})([0-9]-)([0-9]{2}-)[0-9]{4}\$

^([[:alpha:]]|[0-9])([0-9]{2}-){2}[0-9]{4}\$

^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?\$

^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?\$

^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?\$

^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?\$

^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{9}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?\$

This will actually be able to pull back every single format that I need..

Now, for a serious question, How in the world can I make this into one ' ' string so i can pass it into my select case statement?

• ###### 27. Re: REGEXP_LIKE, oh where for art thou?

Hi,

Xev Bellringer wrote:

... I'd still love to see all that logic squashed into a single, minimal regexp - if possible lol Would be interesting never-the-less lol

...

There's a simple but uninteresting way to do it; just combine all the expressions with |.

REGEXP_LIKE ( testcol
,  '(^([[:alpha:]]|[[:alpha:]]{3})[0-9]{6}\$)|'                                                ||
'(^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?\$)|'    ||
'(^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{9}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?\$)|'    ||
...
)

Since each pattern begins with ^ and ends with \$, you can factor those out.

You might consider combining some of the similar patterns, like this:

REGEXP_LIKE ( testcol
,  '^([[:alpha:]]|[[:alpha:]]{3})[0-9]{6})|'                                                  ||
'(([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8,9}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?)|'   ||
...
'\$'
)

• ###### 28. Re: REGEXP_LIKE, oh where for art thou?

Opps, blush, I was meaning this other program I have that is doing the loop.

This right here:

set serveroutput on size unlimited

execute DBMS_OUTPUT.ENABLE (buffer_size => NULL);

------------------------------------------------------------------------------------------------------------------------

DECLARE

look_fnd       INTEGER;

------------------------------------------------------------------------------------------------------------------------

BEGIN

FOR t  IN (SELECT owner, table_name, column_name

FROM all_tab_columns

WHERE owner = upper('&SCHEMA_NAME')

AND table_name in (select table_name from all_tables

where owner = 'ZZZ')) /*Modified to search all tables in ZZZ*/

------------------------------------------------------------------------------------------------------------------------

LOOP

BEGIN

EXECUTE IMMEDIATE 'with a as ( select  case when REGEXP_LIKE(' || t.column_name ||

/* This is where I pass in the regexp string */

',''^(19|20)\d\d[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])\$'')

then ''Match Found''

else ''No Match Found'' end as output from ' || t.owner || '.' || t.table_name || ')

select count(*) from a where a.output=''Match Found'' '

INTO look_fnd ;

IF look_fnd > 0         THEN

DBMS_OUTPUT.put_line (

t.owner

|| '.'

|| t.table_name

|| ' '

|| t.column_name

|| ' '

|| look_fnd);

END IF;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (

'Genric Error '

|| t.column_name

|| ' from '

|| t.owner

|| '.'

|| t.table_name);

END;

END LOOP;

END;

/

Now, since i have to use this program, how can I get all those REGEXP's involved?

• ###### 29. Re: REGEXP_LIKE, oh where for art thou?

Frank Kulash wrote:

There's a simple but uninteresting way to do it; just combine all the expressions with |.

Agreed, that was why I mentioned "minimal" - that's usually the real challenge with those things (and often interesting solutions).  I like seeing them, personally, I usually learn a trick or two from them (even if the answer as a whole isn't entirely practical in a prod solution )

1 2 3 4