In this article, we will learn how to set up an Oracle instance, create sample data and understand the use of the substring function.
Introduction
Structured data is hosted typically in relational database management systems in data objects like tables. Tables have attributes that have a data type associated with them to accommodate various types of data. One of the most common data types is text or string-based data types and one of the most common functions associated with the manipulation of string-based data types is the Substring function. Oracle has been one of the leaders in the database management system and it also supports string-based data types as well as a substring function for extracting desired text from the data.
Setup Oracle Instance
We would need an Oracle instance to use the Oracle Substring function. Setting up an Oracle instance locally can be resource intensive as one would need a reasonable amount of hardware capacity on the system, as well as an Oracle license as well. This can be too much considering that we intend to use the Oracle instance for a very short time. One of the best ways to economically set up an Oracle instance is by setting it up on the cloud. In this article, for our demonstration purposes, we will set it up on AWS cloud as it natively provides Oracle database engine as one of the options for its Relational Database Service (RDS). It is assumed that one already has access to AWS and the RDS service.
Assuming that one has access to an AWS account, log in to the AWS console and open the home page of Amazon RDS service. On the home page, we would find the Create Database button as shown below to invoke the database creation wizard.
The first step would be to use one of the two options – Standard Create and Easy Create. The difference here is that in the Easy Create option, the recommended and best practice options would be automatically selected. While in the Standard Create option, we would get to select and configure the options as desired. In our case, we would select the default Standard Create option. After this, we need to select the database engine. We have six different engines to select from, and in our case, we will be selecting Oracle as shown below.
By default, Amazon RDS is the service we are going to use which lets us customize and manage the database instance as desired. In case, if we intend to customize options at an OS and infra level, we can select RDS custom as well. For now, we would proceed with all the default options as shown below. One of the key reasons to set up an Oracle instance on AWS was the ease of manageability and the licensing option. As seen below, we can choose the license included option, so that those who do not have an Oracle license, do not need to procure it and can pay for it only for the short duration for which we would be using it.
After selecting the licensing options, we need to select one of the templates for deployment. We would be using it for demo purposes, so we can select the Dev/Test template. Then we need to provide a name for the database as shown below.
In this step, we would be setting up the credentials for the admin to log on to the database instance. Provide the username and password as shown below and proceed to the next step.
Now we are moving toward the configuration where we will configure options related to instance size and accessibility. For now, we will proceed with the default option as shown below.
As we are setting up this instance on AWS cloud and we would be using an IDE from the local system to access the instance hosted on the cloud, either one must have a VPN connectivity to the instance if the instance is placed in a private network or it should be alternatively hosted in a public subnet. In our case, to keep the network configuration simple, we will host this instance on the public network by setting selecting the value of Yes for the Public Access option.
Proceed with the rest of the default options and deploy the database instance. Once the instance is deployed, one would need to install an IDE to connect to the instance so that we can execute our DDL and DML commands on this database instance. There are many IDE options available and one can choose any that can connect to the instance that we just created. I choose to use the DBeaver tool that can connect to a variety of databases including the Oracle instance hosted on Amazon RDS service.
It is assumed now that one has installed the IDE and connected to the Oracle instance using the same. Navigate to the query editor from where we would execute the Oracle substring function. Before we get started with the use of the Oracle substring function, let’s take a quick look at the syntax of this function. A pictorial syntax of the Oracle substring function is shown below. There are different variations of the substr function. In our case, we will focus on the substr function. The other variants work in a near similar way. SUBSTRB uses bytes, SUBSTRC uses Unicode, SUBSTR2 uses UCS2 code points and SUBSTR4 uses UCS4 code points. This is the only difference between SUBSTR and its other variants. The rest of the syntax is quite simple, it expects the starting position and length of the string to be extracted as the input parameters along with the actual string.
Now that we understand the syntax of the Oracle substring function, let’s use see it in action by executing a query with this function. Execute the SQL query containing the Oracle substring function as shown below. In this example, we are passing the text “Oracle Substring Function” as the input parameter and passing the starting position as well as the length of the extracted text as input parameters. The output can be seen in the output pane in the bottom half of the editor.
At times, one may want to specify the position of the text from reverse i.e., from the right instead of left. In such cases, we can specify the starting position with a minus sign as shown below. And if we specify the same length of the text to be extracted, we will get the identical result as shown below.
So far, we have been using the Oracle substring function on static text. Let’s quickly create a sample data structure with data and apply this function to it. We can create a simple table by executing a SQL script as shown below.
Add some records to this table. I have added a single record to this table so that we can use it with the Oracle substring function as shown below.
Let’s say that we want to extract the initial from the first name field. We can use the Oracle Substring function in the query as shown below. The usage here is identical to the above queries, the only difference being instead of using a static text value, we have passed the name of the field as the input parameter, and we get the initial in the query result as shown below.
In this way, we can use the Oracle substring function which is quite simple in usage but very effective and handy for extracting desired text values.
Conclusion
In this article, we learned how to set up an Oracle instance on the cloud and use the Oracle Substring function on text values for extracting text from the desired location and of the desired length.
She has a deep experience in designing data and analytics solutions and ensuring its stability, reliability, and performance. She is also certified in SQL Server and have passed certifications like 70-463: Implementing Data Warehouses with Microsoft SQL Server.
View all posts by Gauri Mahajan
- Oracle Substring function overview with examples - June 19, 2024
- Introduction to the SQL Standard Deviation function - April 21, 2023
- A quick overview of MySQL foreign key with examples - February 7, 2023