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.
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.
The substring function syntax in Oracle is:
SUBSTR (String, start position, [substring length])
Arguments or Parameters
The substring function has below arguments or parameters:
The input string value
The starting position in the string from where the extraction will start.
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.
The SUBSTR function return type includes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
Currently, substring in Oracle SQL is available for the below version of Oracle: Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
The substring function supports
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’:
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.
TRIM ([ [ LEADING | TRAILING | BOTH] <trim character> FROM ] <string> )
Arguments or Parameters
The TRIM function will remove the string from the start of the string
The TRIM function will remove the string from the end of the string
The TRIM function will remove the string from both the start and end of the string
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.
The input strings
The trim function returns only a string value
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
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 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.
UPPER (< string >)
Arguments or Parameters
< string >
The Upper function returns a string value
Like substring in Oracle SQL, the UPPER function supports in below version of Oracle: Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
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.
LOWER (< string >)
Parameters or Arguments
< string >
The LOWER function returns a string value
Currently, the LOWER function supports in below version of Oracle SQL: Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
The below example used the lowercase string ‘HELLO as input to the UPPER function, the function returns the output in uppercase as ‘hello’:
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’.
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.