Spark SQL defines built-in standard String functions in DataFrame API, these String functions come in handy when we need to make operations on Strings. In this article, I will explain the usage of some functions with scala examples. You can access the standard functions using the following import statement.
// Import spark sql functions
import org.apache.spark.sql.functions._
Related: If you are looking for PySpark, please refer to PySpark SQL String Functions
<a id="h-spark-sql-string-functions"></a>
Spark SQL String Functions:
Click on each link from below table for more explanation and working examples of String Function with Scala example.
| STRING FUNCTION SIGNATURE | STRING FUNCTION DESCRIPTION |
| --- | --- |
| ascii(e: Column): Column | Calculates ascii() value of the first character of the string. It returns an integer value. |
| base64(e: Column): Column | It is used to encode a binary column into a Base64-encoded string. |
| concat_ws(sep: String, exprs: Column*): Column | Used to concatenate multiple string or column values together into a single string, with a specified separator between each value. |
| decode(value: Column, charset: String): Column | Decodes Base64-encoded strings back into their original binary form. using the provided character set (one of 'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'). |
| encode(value: Column, charset: String): Column | Converts binary data into a Base64-encoded string, facilitating textual representation for transmission and storage. |
| format_number(x: Column, d: Int): Column | Formats a numeric value to a specified number of decimal places. |
| format_string(format: String, arguments: Column*): Column | Formats strings using placeholders, similar to the printf function in other programming languages. |
| initcap(e: Column): Column | Capitalizes the first letter of each word in a string |
| instr(str: Column, substring: String): Column | Returns the position of the first occurrence of a substring within a string. |
| length(e: Column): Column | Computes the character length of a given string or number of bytes of a binary string. |
| lower(e: Column): Column | Converts a string column to lower case. |
| levenshtein ( l : Column , r : Column ) : Column | Computes the Levenshtein distance of the two given string columns. |
| locate(substr: String, str: Column): Column | Returns the position of the first occurrence of a substring within a string. |
| lpad(str: Column, len: Int, pad: String): Column | Pads a string with specified characters on the left side until it reaches the desired length. |
| ltrim(e: Column): Column | Removes leading whitespace characters from a string. |
| regexp_extract(e: Column, exp: String, groupIdx: Int): Column | Extracts substrings from a string column based on a regular expression pattern and returns the matched groups. |
| regexp_replace(e: Column, pattern: Column, replacement: Column): Column | Replaces substrings in a string column that match a regular expression pattern with a specified replacement string. |
| unbase64(e: Column): Column | Decodes Base64-encoded strings back into their original binary form.. This is the reverse of base64. |
| rpad(str: Column, len: Int, pad: String): Column | Pads a string with specified characters on the right side until it reaches the desired length. |
| repeat(str: Column, n: Int): Column | Repeats a string or character a specified number of times. |
| rtrim(e: Column): Column | Removes trailing whitespace characters from a string. |
| soundex(e: Column): Column | Computes the Soundex code of a string, a phonetic algorithm for indexing names by sound. |
| split(str: Column, regex: String): Column | Splits a string into an array of substrings based on a delimiter. |
| substring(str: Column, pos: Int, len: Int): Column | Extracts a substring from a string column, starting at a specified position and optionally up to a specified length. |
| substring_index(str: Column, delim: String, count: Int): Column | Extracts a substring from a string column before or after a specified delimiter occurrence. * If count is positive, everything the left of the final delimiter (counting from left) is returned. *If count is negative, every to the right of the final delimiter (counting from the right) is returned. substring_index performs a case-sensitive match when searching for delim. |
| overlay(src: Column, replaceString: String, pos: Int, len: Int): Column | Replaces part of a string with another string starting at a specified position and optionally for a specified length. |
| translate(src: Column, matchingString: String, replaceString: String): Column | Replaces characters in a string based on a mapping of each character to its replacement. |
| trim(e: Column): Column | Removes leading and trailing whitespace characters from a string. |
| trim(e: Column, trimString: String): Column | Trim the specified character from both ends for the specified string column. |
| upper(e: Column): Column | Converts all characters in a string column to upper case. |
<a id="h-conclusion"></a>
Conclusion:
Spark SQL string functions provide a powerful set of tools for manipulating and analyzing textual data within Apache Spark. These functions allow users to perform a wide range of operations, such as string manipulation, pattern matching, and data cleansing.
Happy Learning !!
<a id="h-related-articles"></a>