Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

How to Parse a String in Oracle SQL – Querychat

In this example, we will discover how to operate with comma-separated values and the values that we need to be separate. In this article, we will find out how to do this with the use of REGEXP_SUBSTR.

Oracle REGEXP_SUBSTR syntax

In Oracle, we have a REGEXP_SUBSTR, which can help us to split the comma-separated values. However, this expression can be used not only to split the values with separators but also just to split values, and here we will consider both options.

REGEXP_SUBSTR makes it possible to search for substrings based on regular expressions. As a result, we get exactly the part of the string that corresponds to the specified regular expression, and not the position of the substring. 

We can receive data in two types, VARCHAR2 or CLOB. The character set will correspond to the source string.

Let’s look at the syntax of this function: 

REGEXP_SUBSTR(source, reg_exp

              [, start

                 [, appear

                    [, correspondence

                       [, sub_expr

                       ]

                    ]

                 ]

              ]

             )

Where: 

  • source is the string we will be looking for.
  • reg_exp is literally a template which the program will use for the search.
  • start is the place where the search will be started from in the source. It will be 1 by default. Optional.
  • appear is the number that indicates which matching result should be shown in the output. The default is 1, and this means that the first matching string that appears on the way of  our search will be shown. Optional.
  • correspondence is the string that defines the default mapping behavior for the function.

sub_expr indicated the target sub-expression in the regular one. Optional.

The possible correspondence values
i case-insensitive correspondence.
c case-sensitive correspondence.
n allows the period.
m makes the Oracle address our string as a multiline one.

Table 1. The possible correspondence values

Oracle SQL parse string examples

In the first example, we will consider the SQL parsing of the string separated by commas, and in the second one, we will discover how to separate the string with other symbols.

Example 1. Dividing Comma Separated Values

In our first example, we will search the string for the first substring on our way that is separated with commas from both sides. 

It will work like that. Oracle will look for a comma followed by a character or several characters that are not commas. As a result, we get a substring surrounded by commas.

The script we will be using is: 

SELECT

REGEXP_SUBSTR(' 105 E Goff Ave, Granada, CO 81041',

',[^,]+,') "REGEXPR_SUBSTR"

FROM DUAL;

Result:

Figure 1. The result of the REGEXP_SUBSTR

As a result, we have Granada surrounded by commas.

Example 2. Dividing  a string

In our second example, we will initiate the search for “http://” that must be followed by more alphanumeric characters and optionally, a period (.).

Oracle searches for a minimum of 3 and a maximum of 4 compliances of this substring between “http://” and either a slash (/) or the end of the string.

The script we will be using is: 

SELECT

REGEXP_SUBSTR('https://www.got-it.ai/solutions',

              'https://([[:alnum:]]+\.?){3,4}/?') "REGEXP_SUBSTR"

FROM DUAL;

Result: 

Figure 2. The result of the REGEXP_SUBSTR

As a result, we have “https://www.got” as the string ends before the “-”.

Instant livechat to an Expert!

Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service! 

Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc