Home » SQL & PL/SQL » SQL & PL/SQL » REGEXP_REPLACE IN A CASE STATEMENT (Oracle PS/SQL Developer)
REGEXP_REPLACE IN A CASE STATEMENT [message #636645] Wed, 29 April 2015 12:05 Go to next message
headrj
Messages: 4
Registered: April 2015
Location: UAB
Junior Member
I have an input string with numerous formats. It might be:

NCA385Useless Text
NCA 386:Useless Text
NC345/545Some Useless Text
NCA400/500 Some Useless Text
NC 300/500Some Useless Text
NC 450/550(Some Uselss Text)
NCA 200 Useless Text
NCA 220/520:Useless Text
NC250 (Some Useless Text)
NC 300 (Some Useless Text)

I want to use this field in an ORACLE CASE STATEMENT WITHIN A VIEW (i.e. CREATE OR REPLACE VIEW...) like this:

case
when (course_no like '%') then
REGEXP_REPLACE ???
end course_id

I know enough about regular expressions to handle some of this, yet there are so many patterns to that data, that
guessing all of them is just impossible. The hoped for outcome of "course_id" from the input data above will be
in a column all its own (within the view) represented the following way:

NCA385
NCA386
NC345/545
NCA400/500
NC300/500
NC450/550
NCA200
NCA220/520
NC250
NC300

The input variable will always be (from the front of the variable) ^[A-Z]{2,4}. After that, there might (or might
not) be one or two spaces followed by 3 digits, followed (perhaps) by a forward slash and three more digits. If
there is no forward slash, the rest of the input variable is useless and need not be returned. If there is a for-
ward slash, return the slash and the three following digits. Eliminate any space between the last of the beginning
alphas and the first encountered digit. It just seems that at some point you could use the pipe | as a logical OR
to expand the pattern match. Any help you can provide me will be greatly appreciated.

Report message to a moderator

Re: REGEXP_REPLACE IN A CASE STATEMENT [message #636646 is a reply to message #636645] Wed, 29 April 2015 12:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Example of a test case:
drop table t;
create table t (val varchar2(50));
insert into t values ('NCA385Useless Text');
insert into t values ('NCA 386:Useless Text');
insert into t values ('NC345/545Some Useless Text');
insert into t values ('NCA400/500 Some Useless Text');
insert into t values ('NC 300/500Some Useless Text');
insert into t values ('NC 450/550(Some Uselss Text)');
insert into t values ('NCA 200 Useless Text');
insert into t values ('NCA 220/520:Useless Text');
insert into t values ('NC250 (Some Useless Text)');
insert into t values ('NC 300 (Some Useless Text)');
commit;

Here's the starter:
SQL> select val,
  2         regexp_substr(val, '^[A-Z]{2,4} {0,2}\d{3}(/\d{3})?') res
  3  from t
  4  /

VAL                                                RES
-------------------------------------------------- ---------------------
NCA385Useless Text                                 NCA385
NCA 386:Useless Text                               NCA 386
NC345/545Some Useless Text                         NC345/545
NCA400/500 Some Useless Text                       NCA400/500
NC 300/500Some Useless Text                        NC 300/500
NC 450/550(Some Uselss Text)                       NC 450/550
NCA 200 Useless Text                               NCA 200
NCA 220/520:Useless Text                           NCA 220/520
NC250 (Some Useless Text)                          NC250
NC 300 (Some Useless Text)                         NC 300


Please feedback.

Report message to a moderator

Re: REGEXP_REPLACE IN A CASE STATEMENT [message #636647 is a reply to message #636646] Wed, 29 April 2015 12:30 Go to previous messageGo to next message
headrj
Messages: 4
Registered: April 2015
Location: UAB
Junior Member
What could be done to delete the space(s) between, say "NC" and "405"?

Report message to a moderator

Re: REGEXP_REPLACE IN A CASE STATEMENT [message #636648 is a reply to message #636647] Wed, 29 April 2015 12:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Many things.
I gave you most of the job, try to do the rest and come back when you are done with the solution or where you are stuck if you can't get it.

Report message to a moderator

Re: REGEXP_REPLACE IN A CASE STATEMENT [message #636649 is a reply to message #636648] Wed, 29 April 2015 12:51 Go to previous messageGo to next message
headrj
Messages: 4
Registered: April 2015
Location: UAB
Junior Member
I'd gone with this before I knew you'd replied. I get a compiler error.

case
when (course_no like '%') then
REGEXP_SUBSTR(course_no, '^[A-Z]{2,4} {0,2}\d{3}(/\d{3})?') some_substr
REGEXP_REPLACE(some_substr, '\s','')
end course_id,

In the case, you can either use a substr or a replace, but not both. Nor can you use UPDATE at the end of "CREATE OR REPLACE VIEW..."

Report message to a moderator

Re: REGEXP_REPLACE IN A CASE STATEMENT [message #636650 is a reply to message #636649] Wed, 29 April 2015 13:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

You can embed a function inside a function:
replace(regexp_substr(.....),' ')

Report message to a moderator

Re: REGEXP_REPLACE IN A CASE STATEMENT [message #636652 is a reply to message #636650] Wed, 29 April 2015 14:02 Go to previous messageGo to next message
headrj
Messages: 4
Registered: April 2015
Location: UAB
Junior Member
C'est tellement TRES bizarre. Certainment. Is the following a correct assumption? The 'REPLACE' seeks out blanks from the resultant 'SUBSTR' function. Finding them, it replaces them with nothing, since there is nothing following the ' ' in the REPLACE. I inserted $ in the ' ' and the spaces remained in the resultant data, I assume, because the REPLACE was looking for a $ sign AND did not find one, so the blanks remained.

Aujourd hui, vous etes mon hero.

Report message to a moderator

Re: REGEXP_REPLACE IN A CASE STATEMENT [message #636653 is a reply to message #636652] Wed, 29 April 2015 14:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
Finding them, it replaces them with nothing, since there is nothing following the ' ' in the REPLACE.


Correct.

Quote:
I inserted $ in the ' ' and the spaces remained in the resultant data, I assume, because the REPLACE was looking for a $ sign AND did not find one, so the blanks remained.


Correct, replace is not regexp_replace, it does not search for a regular expression but for the string itself (here a space in my expression or a space followed by a dollar sign in your test).

SQL> select val,
  2         replace(regexp_substr(val, '^[A-Z]{2,4} {0,2}\d{3}(/\d{3})?'),' ') res
  3  from t
  4  /

VAL                                                RES
-------------------------------------------------- --------------------------------
NCA385Useless Text                                 NCA385
NCA 386:Useless Text                               NCA386
NC345/545Some Useless Text                         NC345/545
NCA400/500 Some Useless Text                       NCA400/500
NC 300/500Some Useless Text                        NC300/500
NC 450/550(Some Uselss Text)                       NC450/550
NCA 200 Useless Text                               NCA200
NCA 220/520:Useless Text                           NCA220/520
NC250 (Some Useless Text)                          NC250
NC 300 (Some Useless Text)                         NC300

Report message to a moderator

Re: REGEXP_REPLACE IN A CASE STATEMENT [message #636654 is a reply to message #636653] Wed, 29 April 2015 14:12 Go to previous message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

But you can also do it with regexp_replace:
SQL> select val,
  2         regexp_replace(val, '^([A-Z]{2,4}) {0,2}({0,2}\d{3}(/\d{3})?).*$', '\1\2') res
  3  from t
  4  /

VAL                                                RES
-------------------------------------------------- ------------------------------
NCA385Useless Text                                 NCA385
NCA 386:Useless Text                               NCA386
NC345/545Some Useless Text                         NC345/545
NCA400/500 Some Useless Text                       NCA400/500
NC 300/500Some Useless Text                        NC300/500
NC 450/550(Some Uselss Text)                       NC450/550
NCA 200 Useless Text                               NCA200
NCA 220/520:Useless Text                           NCA220/520
NC250 (Some Useless Text)                          NC250
NC 300 (Some Useless Text)                         NC300

[Updated on: Wed, 29 April 2015 14:12]

Report message to a moderator

Previous Topic: When was a tablespace created?
Next Topic: How to fetch rows having only numbers in a varchar2 column
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Jul 31 20:14:03 CDT 2026
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.