EDUCBA

Home Software Development Software Development Tutorials PL/SQL Tutorial PLSQL string functions

PLSQL string functions

Updated April 5, 2023

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:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

  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