The Oracle SQL database provides many useful functions to use with your query or programming. The SUBSTR function is one of the most used string functions in the Oracle SQL database. I will also discuss a few important string functions TRIM, UPPER, and LOWER which can be used along with substring functions. In this article, we will discuss substring in Oracle SQL.
Introduction
One of the most popular relational databases is Oracle SQL Database, it is also known as OracleDB or Oracle. Currently, Oracle corporation owned the Oracle database. Larry Ellison and his co-workers together developed the original version of the Oracle relational database. Oracle database provides many features including high availability which means the database is available all the time by providing 24×7 availability, and scalability which means the database can be scaled up or scaled down as per the application’s requirement, Oracle provides one of the top-level security features which includes TDE (Transparent Data Encryption) Security Oracle database provides a very high level of security to prevent unauthorized access to the database. It supports many important security features including TDE (Transparent Data Encryption) which supports data encryption at source and destination Data Redaction., analytics, Management, etc. Oracle database supports multiple platforms which means it can run on different hardware and operating system including Windows, Unix, Linux, and GNU distributions. Oracle is one of the first databases which supports GNU open technologies.
Let us start and talk about the substring function in Oracle.
Oracle provides various functions to work with string values, for example, LENGTH, SUBSTR, TRIM, LOWER, REPLACE and CONVERT. The Oracle functions can be used in various ways, it can be used in a SQL statement, or you can also write your own programming in Oracle SQL by using stored procedures, triggers, etc.
The SUBSTR function
The substring function in Oracle SQL is one of the most commonly used functions. Oracle provides a SUBSTR function to subtract values from a string.
Syntax
The substring function syntax in Oracle is:
SUBSTR (String, start position, [substring length])
Arguments or Parameters
The substring function has below arguments or parameters:
String
The input string value
Start position
The starting position in the string from where the extraction will start.
Substring length
This value specified the number of characters to be extracted. This parameter is optional which means if you do not specify any value in this parameter, it will return the whole string.
Return Type
The SUBSTR function return type includes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
Availability
Currently, substring in Oracle SQL is available for the below version of Oracle: Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
Input Parameters
The substring function supports
Examples
In the below examples, we are using the string value ‘HELLO’ with a different combination of start position and substring length.
I used the query SUBSTR(‘HELLO’,0,1) for the example below, here start position 0 means it will start from position 1 or the first character of the string then it will extract 1 character from the string ‘Hello’:
Now in the next example the query is SUBSTR(‘HELLO’,2,2), here the start position is 2 which is ‘E’ and it will extract 2 characters from the string ‘Hello’ so the result is ‘EL’:
Let us take another example of a substring in Oracle SQL. I used the query SUBSTR (‘HELLO’, -2, 2), note that the negative number used in the start position, here the negative number -2 at the start position means it will start from the opposite direction of the string ‘HELLO’ which means 2nd position from starting from backward of the string ‘HELLO’ i.e., ‘L’. The substring length is 2 so the result of this query is ‘LO’
In the last example of a substring in Oracle SQL, I used only the start position in the input and removed the substring length value. So, the output value started from position 1 and returned the remaining string value which is ‘Hello’:
The TRIM function
The Oracle SQL trim function removes all specified characters from a string.
Syntax
TRIM ([ [ LEADING | TRAILING | BOTH] <trim character> FROM ] <string> )
Arguments or Parameters
Leading
The TRIM function will remove the string from the start of the string
Trailing
The TRIM function will remove the string from the end of the string
Both
The TRIM function will remove the string from both the start and end of the string
Trim character
The character that will be removed from the string. If this parameter is not specified, then it will remove the space character from the string.
String
The input strings
Return value
The trim function returns only a string value
Availability
Similar to the substring function in Oracle SQL, the TRIM function supports in below version of Oracle: Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
Examples
In the below example, the input string is ‘ HELLO ‘ which contains spaces on both the front and end of the string value. So, the TRIM function removed both the leading and trailing space from the given input string and the output string is ‘HELLO’:
In the next example below, I explicitly mentioned removing the space value from the given string value ‘ HELLO ‘, as a result, the output is ‘HELLO’:
Now, I have specified LEADING in the TRIM function to remove ‘0’ from the string ‘00012345’, so the output value is ‘12345’:
In the next example, I have specified TRAILING in the TRIM function to remove ‘1’ from the string ‘HELLO01, so the output value is ‘HELLO:
The substring in Oracle SQL is used to extract a portion of a string and the TRIM function is used to remove specific strings in the input.
In the last example of the TRIM function, I mentioned BOTH parameters which means removing ‘1’ from both the front and end of the given string ‘123HELLO111’. So, the output value is ‘23HELLO’:
The UPPER Function
The UPPER function converts an input string to uppercase. If the string contains characters other than letters then they are not affected by the UPPER function.
Syntax
UPPER (< string >)
Arguments or Parameters
< string >
Return value
The Upper function returns a string value
Availability
Like substring in Oracle SQL, the UPPER function supports in below version of Oracle: Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
Example
The below example used the lowercase string ‘hello’ as input to the UPPER function, the function returns the output in uppercase as ‘HELLO’
The LOWER Function
Now I will discuss another function that can be used along with substring in Oracle SQL. The LOWER function converts an input string to a lowercase. If the string contains characters other than letters then they are not affected by the UPPER function.
Syntax
LOWER (< string >)
Parameters or Arguments
< string >
Return value
The LOWER function returns a string value
Availability
Currently, the LOWER function supports in below version of Oracle SQL: Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
Example
The below example used the lowercase string ‘HELLO as input to the UPPER function, the function returns the output in uppercase as ‘hello’:
Use two functions together
We can also combine the substring function with other functions to get the desired result. For example, we can combine SUBSTR and UPPER functions so that we can extract the string and convert the specified string to an uppercase letter at the same time. Let us look at the below example –
In the above example, we are using another function with substring in Oracle SQL. The SUBSTR function which is using the input string hello and generated output he, at the same time the UPPER function is converting the word ‘he’ to the uppercase letter ‘HE’.
Like the above example, I used the LOWER function with SUBSTR. The inner SUBSTR function extracts the string’s value ‘HE’ then the outer LOWER function converts the uppercase ‘HE’ to lowercase ‘he’.
Conclusion
In this article, we learned substring in Oracle SQL and its syntax, availability, and usage. Along with the substring function, we also discussed TRIM, UPPER, and LOWER functions.
Arindam has rich experience in Azure solution implementation and support activities for large enterprise clients, having worked on multiple developments and enhancement projects. He is
currently based in India and is working as a Technical Lead for a leading MNC Company.
A few significant facts about his career:
* Has over 10.7 years of IT experience in Microsoft platforms.
* Has around 8 years of experience in SQL database development (T-SQL/Performance tuning/CDC) and ETL-SSIS.
* Has 5 years' experience in Azure Environment (Azure Data Factory, Azure Data Lake Storage, Azure SQL Database, Azure Cosmos DB, Azure Synapse Analytics)
- Understanding the SQL IN operator with examples - March 19, 2024
- An introduction to PostgreSQL Data Types with examples - September 15, 2023
- Understanding Substring in Oracle SQL - March 22, 2023