PLSQL string functions

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:

1. ASCII(x): Provides the ASCII value of the x character given.
2. CHR(x): Provides the character having an ASCII value of x.
3. INITCAP(x): Transforms the first letter of every word in x to uppercase with returning that string.
4. CONCAT(x, y): It provides the appended string by concatenating the given strings, i.e., x and y.
5. LENGTH(x): Outputs the number of x characters.
6. LENGTHB(x): It provides the length of a character string for only byte characters set in bytes.
7. INSTR(x, find_string[, start] [, occurrence]): It searches for the find_string in x by returning the location at which it occurs.
8. INSTRB(x): It delivers the position of a string inside another string however outputs the value in bytes.
9. LOWER(x): It returns the string provided by converting the letters given in x to lowercase.
10. LTRIM(x, [trim_string]): It helps for trimming the characters from the left.
11. 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.
12. 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.
13. NLS_INITCAP(x): Similar to the INITCAP string function, excluding that it may apply a variant sort technique as stated by NLSSORT.
14. NLS_UPPER(x): Similar to the UPPER string function excluding that it may apply a variant sort technique as stated by NLSSORT.
15. NLS_LOWER(x): Similar to the LOWER string function excluding that it may apply a variant sort technique as stated by NLSSORT.
16. 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.
17. NVL(x, value): Outputs value if x has null value, else it returns x itself.
18. 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.
19. 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.
20. RPAD(x, wiProdth [, pad_string]): The x value is padded to the right as provided.
21. RTRIM(x [, trim_string): The value of x is trimmed from the right.
22. 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.
23. 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.
24. SOUNDEX(x): Results a string including the phonetic representation of x value.
25. UPPER(x): It returns the string provided as an argument by converting the letters to uppercase given in x.
26. TRIM([trim_char FROM] x): It helps to trim the characters from the left and right of x.
27. CONVERT(x, character_set1, character_set2): It converts the given x value of the character string from one character set to the next one.
28. 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.
29. REGEXP_COUNT(‘x’, ‘\d’): It outputs the number of times a given pattern has occurred in a string.
30. REGEXP_INSTR(‘x’, ‘\d+’): It returns the position of a provided pattern in a given string.
31. REGEXP_LIKE(‘x’, ‘\d+’): It outputs the position of a provided pattern in a given string.
32. REGEXP_REPLACE(‘x’, ‘\d+’, ‘replace_string’): It substitutes substring in a given string by a new substring by means of a regular expression.
33. REGEXP_SUBSTR(‘x’, ‘\d+’): It extracts substrings from a given string by means of a regular expression pattern.
34. 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’.
35. 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).
36. VSIZE(): In Oracle, this function provides the number of bytes for a specified expression.
37. NCHR(): This function provides the character that is based on the national character set on the number_code.
38. DECOMPOSE(): This function results in a Unicode string from the specified string.
39. COMPOSE(): In Oracle, this compose function operates to provide the Unicode string.
40. ASCIISTR(): For a provided character, this function results in the numeric value.
41. 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 –

1. PL/SQL TRIM
2. PL/SQL Date Functions
3. PL/SQL Cursor Loop
4. PLSQL Interview Questions