Updated April 5, 2023
Introduction to PLSQL string functions
- PL/SQL String Function is a series of characters with a non-compulsory size specification where the characters may be numeric, special characters, blank, letters, or a combination of all.
- PL/SQL String Functions comprised of three types of strings:
- Fixed-length strings: In this type, developers need to identify the length while stating the string. Here, the string is known to be right-padded, having spaces to the length so stated.
- Variable-length strings: In this type, there is a maximum length that is up to 32 767 for the string to be stated with n3 o padding that takes place.
- Character large objects: This is CLOBs type, where variable-length strings are up to 128 terabytes.
- Also, PL/SQL Strings can be either literals or variables where the literal string is surrounded inside quotation marks.
String Functions of PLSQL
PL/SQL delivers the concatenation operator, i.e., II, which helps to join two strings. More functions of PL/SQL can be viewed as follows in brief:
- ASCII(x): Provides the ASCII value of the x character given.
- CHR(x): Provides the character having an ASCII value of x.
- INITCAP(x): Transforms the first letter of every word in x to uppercase with returning that string.
- CONCAT(x, y): It provides the appended string by concatenating the given strings, i.e., x and y.
- LENGTH(x): Outputs the number of x characters.
- LENGTHB(x): It provides the length of a character string for only byte characters set in bytes.
- INSTR(x, find_string[, start] [, occurrence]): It searches for the find_string in x by returning the location at which it occurs.
- INSTRB(x): It delivers the position of a string inside another string however outputs the value in bytes.
- LOWER(x): It returns the string provided by converting the letters given in x to lowercase.
- LTRIM(x, [trim_string]): It helps for trimming the characters from the left.
- LPAD(x, width [, pad_string]): This function pads x with spaces to the left so that we can bring the total length of the string to the width characters.
- NANVL(x, value): If the x equals the NaN type special value but not a number, then it returns a value, else x is reverted.
- NLS_INITCAP(x): Similar to the INITCAP string function, excluding that it may apply a variant sort technique as stated by NLSSORT.
- NLS_UPPER(x): Similar to the UPPER string function excluding that it may apply a variant sort technique as stated by NLSSORT.
- NLS_LOWER(x): Similar to the LOWER string function excluding that it may apply a variant sort technique as stated by NLSSORT.
- NLSSORT(x): Modifies the technique of sorting the characters. But it must be stated before any NLS string function; else, it uses the default sort technique.
- NVL(x, value): Outputs value if x has null value, else it returns x itself.
- NVL2(x, value1, value2): If the given x value is not null, then it will return 1, but if the x’s value is null, then the value2 will be returned as a result.
- REPLACE(x, search_string, replace_string): In this function, the x value is searched for search_string and is replaced with the replace_string as provided as arguments.
- RPAD(x, wiProdth [, pad_string]): The x value is padded to the right as provided.
- RTRIM(x [, trim_string): The value of x is trimmed from the right.
- SUBSTR(x, start [, length]): It results in a substring of x, which initiates at the position stated by start. There may be an extra optional length be supplied for the substring.
- SUBSTRB(x): Similar to the SUBSTR string function, excluding that the parameters are stated in bytes as a substitute of characters for the single-byte character systems.
- SOUNDEX(x): Results a string including the phonetic representation of x value.
- UPPER(x): It returns the string provided as an argument by converting the letters to uppercase given in x.
- TRIM([trim_char FROM] x): It helps to trim the characters from the left and right of x.
- CONVERT(x, character_set1, character_set2): It converts the given x value of the character string from one character set to the next one.
- DUMP(x): It produces a string value in VARCHAR2 type, which includes the datatype code with length measured in bytes as well as the internal representation of a defined expression.
- REGEXP_COUNT(‘x’, ‘\d’): It outputs the number of times a given pattern has occurred in a string.
- REGEXP_INSTR(‘x’, ‘\d+’): It returns the position of a provided pattern in a given string.
- REGEXP_LIKE(‘x’, ‘\d+’): It outputs the position of a provided pattern in a given string.
- REGEXP_REPLACE(‘x’, ‘\d+’, ‘replace_string’): It substitutes substring in a given string by a new substring by means of a regular expression.
- REGEXP_SUBSTR(‘x’, ‘\d+’): It extracts substrings from a given string by means of a regular expression pattern.
- TRANSLATE(‘x’, ‘y’, ‘z’): It replaces all the occurrences of specified characters with some other characters present in a string. Suppose in the provided string function as TRANSLATE(‘abdce’, ‘adc’, ‘vz’) results as ‘vbze’.
- Bit_Length(x): It outputs the length of a stated string in bits. Every Unicode character’s length is equal to 16 bits (2 bytes).
- VSIZE(): In Oracle, this function provides the number of bytes for a specified expression.
- NCHR(): This function provides the character that is based on the national character set on the number_code.
- DECOMPOSE(): This function results in a Unicode string from the specified string.
- COMPOSE(): In Oracle, this compose function operates to provide the Unicode string.
- ASCIISTR(): For a provided character, this function results in the numeric value.
- CONCAT_WITH(): This Oracle function outputs a string with the concatenation of all the arguments.
Hence, the PL/SQL delivers the liberty of developing variable-length strings in which providing the size of the string is optional. PL/SQL can be said as a grouping of characters that can be alphabets, special characters, numbers, or all together.
Conclusion – PLSQL string functions
- All the PL/SQL string functions are arranged into the type of function on the basis of types such as date/time, string/character, advanced, conversion, and mathematical/numeric.
- In Oracle, these functions can be implemented in SQL queries or statements; even they can be applied inside the programming environment delivered by the Oracle/PLSQL database like the triggers, stored procedures, and functions, etc.
Recommended Articles
This is a guide to PLSQL string functions. Here we discuss the string functions of PL/SQL and their types in detail and in brief. You may also have a look at the following articles to learn more –
