Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Introduction to regular expressions ...

cd_2Sep 27 2006 — edited Mar 13 2023

I'm well aware that there are already some articles on that topic, some people asked me to share some of my knowledge on this topic. Please take a look at this first part and let me know if you find this useful. If yes, I'm going to continue on writing more parts using more and more complicated expressions - if you have questions or problems that you think could be solved through regular expression, please post them.

Introduction

Oracle has always provided some character/string functions in its PL/SQL command set, such as SUBSTR, REPLACE or TRANSLATE. With 10g, Oracle finally gave us, the users, the developers and of course the DBAs regular expressions. However, regular expressions, due to their sometimes cryptic rules, seem to be overlooked quite often, despite the existence of some very interesing use cases. Beeing one of the advocates of regular expression, I thought I'll give the interested audience an introduction to these new functions in several installments.

Having fun with regular expressions - Part 1

Oracle offers the use of regular expression through several functions: REGEXP_INSTR, REGEXP_SUBSTR, REGEXP_REPLACE and REGEXP_LIKE. The second part of each function already gives away its purpose: INSTR for finding a position inside a string, SUBSTR for extracting a part of a string, REPLACE for replacing parts of a string. REGEXP_LIKE is a special case since it could be compared to the LIKE operator and is therefore usually used in comparisons like IF statements or WHERE clauses.

Regular expressions excel, in my opinion, in search and extraction of strings, using that for finding or replacing certain strings or check for certain formatting criterias. They're not very good at formatting strings itself, except for some special cases I'm going to demonstrate.

If you're not familiar with regular expression, you should take a look at the definition in Oracle's user guide Using Regular Expressions With Oracle Database, and please note that there have been some changes and advancements in 10g2. I'll provide examples, that should work on both versions.

Some of you probably already encountered this problem: checking a number inside a string, because, for whatever reason, a column was defined as VARCHAR2 and not as NUMBER as one would have expected.

Let's check for all rows where column col1 does NOT include an unsigned integer. I'll use this SELECT for demonstrating different values and search patterns:

WITH t AS (SELECT '456' col1
             FROM dual
            UNION 
           SELECT '123x'
             FROM dual
            UNION   
           SELECT 'x123'
             FROM dual
            UNION  
           SELECT 'y'
             FROM dual
            UNION  
           SELECT '+789'
             FROM dual
            UNION  
           SELECT '-789'
             FROM dual
            UNION  
           SELECT '159-'
             FROM dual
            UNION  
           SELECT '-1-'
             FROM dual
          )     
SELECT t.col1
  FROM t
 WHERE NOT REGEXP_LIKE(t.col1, '^[0-9]+$')
;

Let's take a look at the 2nd argument of this REGEXP function: '^[0-9]+$'. Translated it would mean: start at the beginning of the string, check if there's one or more characters in the range between '0' and '9' (also called a matching character list) until the end of this string. "^", "[", "]", "+", "$" are all Metacharacters.

To understand regular expressions, you have to "think" in regular expressions. Each regular expression tries to "fit" an available string into its pattern and returns a result beeing successful or not, depending on the function. The "art" of using regular expressions is to construct the right search pattern for a certain task. Using functions like TRANSLATE or REPLACE did already teach you using search patterns, regular expressions are just an extension to this paradigma. Another side note: most of the search patterns are placeholders for single characters, not strings.

I'll take this example a bit further. What would happen if we would remove the "$" in our example? "$" means: (until the) end of a string. Without this, this expression would only search digits from the beginning until it encounters either another character or the end of the string. So this time, '123x' would be removed from the SELECTION since it does fit into the pattern.

Another change: we will keep the "$" but remove the "^". This character has several meanings, but in this case it declares: (start from the) beginning of a string. Without it, the function will search for a part of a string that has only digits until the end of the searched string. 'x123' would now be removed from our selection.

Now there's a question: what happens if I remove both, "^" and "$"? Well, just think about it. We now ask to find any string that contains at least one or more digits, so both '123x' and 'x123' will not show up in the result.

So what if I want to look for signed integer, since "+" is also used for a search expression. Escaping is the name of the game. We'll just use '^\+[0-9]+$' Did you notice the "\" before the first "+"? This is now a search pattern for the plus sign.

Should signed integers include negative numbers as well? Of course they should, and I'll once again use a matching character list. In this list, I don't need to do escaping, although it is possible. The result string would now look like this: '^[+-]?[0-9]+$'. Did you notice the "?"? This is another metacharacter that changes the placeholder for plus and minus to an optional placeholder, which means: if there's a "+" or "-", that's ok, if there's none, that's also ok. Only if there's a different character, then again the search pattern will fail.

Addendum: From this on, I found a mistake in my examples. If you would have tested my old examples with test data that would have included multiple signs strings, like "--", "-+", "++", they would have been filtered by the SELECT statement. I mistakenly used the "*" instead of the "?" operator. The reason why this is a bad idea, can also be found in the user guide: the "*" meta character is defined as 0 to multiple occurrences.

Looking at the values, one could ask the question: what about the integers with a trailing sign? Quite simple, right? Let's just add another '[+-] and the search pattern would look like this: '^[+-]?[0-9]+[+-]?$'.

Wait a minute, what happened to the row with the column value "-1-"?

You probably already guessed it: the new pattern qualifies this one also as a valid string. I could now split this pattern into several conditions combined through a logical OR, but there's something even better: a logical OR inside the regular expression. It's symbol is "|", the pipe sign.

Changing the search pattern again to something like this '^[+-]?[0-9]+$|^[0-9]+[+-]?$' [1] would return now the "-1-" value. Do I have to duplicate the same elements like "^" and "$", what about more complicated, repeating elements in future examples? That's where subexpressions/grouping comes into play. If I want only certain parts of the search pattern using an OR operator, we can put those inside round brackets. '^([+-]?[0-9]+|[0-9]+[+-]?)$' serves the same purpose and allows for further checks without duplicating the whole pattern.

Now looking for integers is nice, but what about decimal numbers? Those may be a bit more complicated, but all I have to do is again to think in (meta) characters. I'll just use an example where the decimal point is represented by ".", which again needs escaping, since it's also the place holder in regular expressions for "any character".

Valid decimals in my example would be ".0", "0.0", "0.", "0" (integer of course) but not ".". If you want, you can test it with the TO_NUMBER function. Finding such an unsigned decimal number could then be formulated like this: from the beginning of a string we will either allow a decimal point plus any number of digits OR at least one digits plus an optional decimal point followed by optional any number of digits. Think about it for a minute, how would you formulate such a search pattern?

Compare your solution to this one:

'^(\.[0-9]+|[0-9]+(\.[0-9]*)?)$'

Addendum: Here I have to use both "?" and "*" to make sure, that I can have 0 to many digits after the decimal point, but only 0 to 1 occurrence of this substrings. Otherwise, strings like "1.9.9.9" would be possible, if I would write it like this:

'^(\.[0-9]+|[0-9]+(\.[0-9]*)*)$'

Some of you now might say: Hey, what about signed decimal numbers? You could of course combine all the ideas so far and you will end up with a very long and almost unreadable search pattern, or you start combining several regular expression functions. Think about it: Why put all the search patterns into one function? Why not split those into several steps like "check for a valid decimal" and "check for sign".

I'll just use another SELECT to show what I want to do:

WITH t AS (SELECT '0' col1
             FROM dual
            UNION
           SELECT '0.'  
             FROM dual
            UNION
           SELECT '.0'  
             FROM dual
            UNION
           SELECT '0.0'  
             FROM dual
            UNION
           SELECT '-1.0'  
             FROM dual
            UNION
           SELECT '.1-'  
             FROM dual
            UNION
           SELECT '.'  
             FROM dual
            UNION
           SELECT '-1.1-'  
             FROM dual
          )  
SELECT t.*
  FROM t
;

From this select, the only rows I need to find are those with the column values "." and "-1.1-". I'll start this with a check for valid signs. Since I want to combine this with the check for valid decimals, I'll first try to extract a substring with valid signs through the REGEXP_SUBSTR function:

NVL(REGEXP_SUBSTR(t.col1, '^([+-]?[^+-]+|[^+-]+[+-]?)$'), ' ')

Remember the OR operator and the matching character collections? But several "^"? Some of the meta characters inside a search pattern can have different meanings, depending on their positions and combination with other meta characters. In this case, the pattern translates into: from the beginning of the string search for "+" or "-" followed by at least another character that is not "+" or "-". The second pattern after the "|" OR operator does the same for a sign at the end of the string.

This only checks for a sign but not if there also only digits and a decimal point inside the string. If the search string fails, for example when we have more than one sign like in the "-1.1-", the function returns NULL. NULL and LIKE don't go together very well, so we'll just add NVL with a default value that tells the LIKE to ignore this string, in this case a space.

All we have to do now is to combine the check for the sign and the check for a valid decimal number, but don't forget an option for the signs at the beginning or end of the string, otherwise your second check will fail on the signed decimals. Are you ready?

Does your solution look a bit like this?

 WHERE NOT REGEXP_LIKE(NVL(REGEXP_SUBSTR(t.col1, 
                           '^([+-]?[^+-]+|[^+-]+[+-]?)$'), 
                       ' '), 
                       '^[+-]?(\.[0-9]+|[0-9]+(\.[0-9]*)?)[+-]?$'
                      )

Now the optional sign checks in the REGEXP_LIKE argument can be added to both ends, since the SUBSTR won't allow any string with signs on both ends. Thinking in regular expression again.

Continued in https://forums.oracle.com/ords/apexds/post/introduction-to-regular-expressions-continued-9561

C.

Fixed some embarrassing typos ... and mistakes.
cd

[Edited by BluShadow 13/03/2023: Fix links in new forum platform]

Comments

Christian.Shay -Oracle

You can only use the Basic connection type for certain types of database connections (specifically, ones where only host/ip, port and service name are sufficient. Connection types that need to use info in tnsnames.ora, sqlnet.ora, or ldap.ora, for example those using directory servers or wallets will not be able to connect using that method.
I am not sure of your database configuration, but perhaps a directory server is involved or perhaps you are using a wallet.
Can you please check the Tns Admin Location for connections that work for you, and please provide:

  1. tnsnames.ora file
  2. tnsalias you are choosing from that file
  3. sqlnet.ora if it exists
  4. ldap.ora if it exists
    (You should redact info of course)
  5. And please provide the info you are entering into the Basic connection, particularly the service name.

Finally, please note that users can paste the connection info from a tnsnames.ora into the Advanced connection type. So if you want you could provide them everything that comes after the "=" (equals sign) from one tnsnames.ora entry to use as a connection string in the Advanced type.

Tim Plößer

Hi,
thank you for the reply!
Just to clarify: when using the TNS Alias connection type every connection saved in the tnsnames.ora files works without any issues/problems, the problems only occur when the basic connection type is used (did also test the advanced connection type - which did also work without any problems for entries copied from the tnsnames.ora files)
Since I do not feel comfortable posting the complete tnsnames.ora file here, here is the (redacted) structure of the alias entry I am trying to reach via the basic connection type (again connection via advanced or tns alias method works flawlessly):
POWERDATENT.ORACLE.XXX.YYY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)
(HOST = POWERDATENT.ORACLE.XXX.YYY)(PORT = 1521)))
(CONNECT_DATA = (SERVICE_NAME = POWERDATENT)))

the sqlnet.ora file contains the following data:
NAMES.DEFAULT_DOMAIN = oracle.XXX.YYY
# Local Naming
NAMES.DIRECTORY_PATH = (TNSNAMES)
# Client
LOG_DIRECTORY_CLIENT = C:\temp
LOG_FILE_CLIENT = client
TRACE_DIRECTORY_CLIENT = C:\temp
TRACE_FILE_CLIENT = client
TRACE_LEVEL_CLIENT = off
TRACE_UNIQUE_CLIENT = on
# tnsping
TNSPING.TRACE_DIRECTORY = C:\temp
TNSPING.TRACE_LEVEL = off
# authentication
SQLNET.AUTHENTICATION_SERVICES= (none)

ldap.ora does not exist in the file location

I have attached the screenshot of the information I am entering into the basic connection type (instead of xxx.yyy I am of course using the real values). When now trying to create the connection I get the ORA-12154 error mentioned in my original post.
basic_connection.pngAs mentioned before for us it is not that big of a deal, since we can fall back to TNS Alias (or now that I know advanced connection) to connect to the DBs which exist in the tnsnames.ora file. However from time to time there is a new server which is not yet in the tnsnames.ora so it would be cool to be able to use the extension instead of having to fall back to sql developer.
And as I said before the identical data used in sql developer or used within python for sqlalchemy does work.

Christian.Shay -Oracle

Hi,
With connection type Basic, can you please try connecting with the same connection info you used before except set Service name to: POWERDATENT.oracle.XXX.YYY
If that does not work, please make a backup copy of sqlnet.ora, then edit sqlnet.ora like so:
Change:
NAMES.DIRECTORY_PATH = (TNSNAMES)
to:
NAMES.DIRECTORY_PATH = (EZCONNECT, TNSNAMES)
Then close VS Code, reopen and try to connect, this time using the original service name: POWERDATENT

Tim Plößer

Hi,
the first solution using POWERDATENT.oracle.XXX.YYY as service name does lead to the same behaviour as before (ORA-12154) error.
The sqlnet.ora file is located on a network drive, where I have no editing rights - so I can't try the second proposed solution.

Christian.Shay -Oracle

You can copy all the files in that shared directory to a non-share directory and then in the Oracle Developer Tools for VS Code extension settings, change the "Config File Folder" and the "Wallet File Folder" settings to the local folder.
Then make the changes I described earlier, restart VS Code, and retry.

Tim Plößer

Hi Christian,
when using local files the change from:
NAMES.DIRECTORY_PATH = (TNSNAMES)
to:
NAMES.DIRECTORY_PATH = (EZCONNECT, TNSNAMES)
Does solve the issue for me.
When doing this I did also actually discover that within the Extension Settings the path was/is set to:
C:\Users\USERNAME\Oracle\network\admin
which was empty (I copied the two .ora files to that location and did the changes there).
I guess to solve this issue more permenantly while still having access to the latest tnsnames.ora files I should talk with one of our DBA to see if the EZCONNECT part can be added to the sqlnet.ora file and point the extension to the network drive, correct?

Christian.Shay -Oracle

Hi,
Glad you got it working.,
Yes, the dba can add EZCONNECT to NAMES.DIRECTORY_PATH on the share. However, he or she could also just delete the entry entirely as the default includes both of them.
Database Net Services Reference (0 Bytes)Could you please update your review on the VS Marketplace to indicate that you identified the issue?

Tim Plößer

Hi,
I did update my review on the VS Marketplace :)
Thank you for the quick and successfull support!

1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 19 2010
Added on Sep 27 2006
42 comments
59,838 views