SQL text and regular expression functions

Clean labels, split strings, and replace patterns with Haybarn SQL. Choose literal string functions for simple edits and regular expressions for patterns.

Choose a function

Build a cleaning expression in stages: trim surrounding spaces, normalize case, then replace the parts that vary. Keep the original column alongside the cleaned value while checking your rules.

Regular expressions interpret punctuation as syntax. For a fixed search string, a literal replacement is easier to reason about. NULL text also needs an explicit policy before combining fields.

Replace text with regular expressions

Clean strings by replacing text that matches a regular expression. Use the g option when every match should be replaced.

A NULL input produces NULL. A string without a match is returned unchanged.

Replace text with regular expressions
SQL
Haybarn WASM 2.0.0-rc12 · In your browser

Guide example · Run to view results.

Expected result
first_matchall_matchesmissing
A# B34A# B#NULL

Watch for: Without the g option, only the first match is replaced. Use replace() when the search text is literal rather than a pattern.

Full regexp_replace() reference →

All text & patterns functions

101 functions in Haybarn WASM 2.0.0-rc12. Browse the filtered catalog →

  • ascii()

    Returns an integer that represents the Unicode code point of the first character of the string.

  • bar()

    Draws a band whose width is proportional to (x - min) and equal to width characters when x = max. width defaults to 80.

  • base64()

    Converts a blob to a base64 encoded string.

  • bin()

    Converts the string to binary representation.

  • bit_length()

    Returns the bit-length of the bit argument.

  • chr()

    Returns a character which is corresponding the ASCII code value or Unicode code point.

  • concat_ws()

    Concatenates many strings, separated by separator. NULL inputs are skipped.

  • contains()

    Returns true if search_string is found within string.

  • decode()

    Converts blob to VARCHAR. Invalid UTF-8 is handled based on the error behavior argument. Can be 'strict' (default, fail), 'replace' to replace invalid characters with '?', or 'ignore' to skip invalid characters.

  • encode()

    Converts the string to BLOB. Converts UTF-8 characters into literal encoding.

  • ends_with()

    Returns true if string ends with search_string.

  • format()

    Formats a string using the fmt syntax.

  • format_bytes()

    Converts integer to a human-readable representation using units based on powers of 2 (KiB, MiB, GiB, etc.).

  • formatReadableDecimalSize()

    Converts integer to a human-readable representation using units based on powers of 10 (KB, MB, GB, etc.).

  • formatReadableSize()

    Converts integer to a human-readable representation using units based on powers of 2 (KiB, MiB, GiB, etc.).

  • from_base64()

    Converts a base64 encoded string to a character string (BLOB).

  • from_binary()

    Converts a value from binary representation to a blob.

  • from_hex()

    Converts a value from hexadecimal representation to a blob.

  • hamming()

    The Hamming distance between to strings, i.e., the number of positions with different characters for two strings of equal length. Strings must be of equal length. Characters of different cases (e.g., a and A) are considered different.

  • hash()

    Returns a UBIGINT with the hash of the value. Note that this is not a cryptographic hash.

  • hex()

    Converts blob to VARCHAR using hexadecimal encoding.

  • hive_partition_component()

    Returns the hive partition directory for a partition column and its value, e.g. name=value. The name and the value are escaped like url_encode does, a NULL value becomes __HIVE_DEFAULT_PARTITION__.

  • ilike_escape()

    Returns true if the string matches the like_specifier (see Pattern Matching) using case-insensitive matching. escape_character is used to search for wildcard characters in the string.

  • instr()

    Returns location of first occurrence of search_string in string, counting from 1. Returns 0 if no match found.

  • jaccard()

    The Jaccard similarity between two strings. Characters of different cases (e.g., a and A) are considered different. Returns a number between 0 and 1.

  • jaro_similarity()

    The Jaro similarity between two strings. Characters of different cases (e.g., a and A) are considered different. Returns a number between 0 and 1. For similarity < score_cutoff, 0 is returned instead. score_cutoff defaults to 0.

  • jaro_winkler_similarity()

    The Jaro-Winkler similarity between two strings. Characters of different cases (e.g., a and A) are considered different. Returns a number between 0 and 1. For similarity < score_cutoff, 0 is returned instead. score_cutoff defaults to 0.

  • lcase()

    Converts string to lower case.

  • left()

    Extracts the left-most count characters.

  • left_grapheme()

    Extracts the left-most count grapheme clusters.

  • length_grapheme()

    Number of grapheme clusters in string.

  • levenshtein()

    The minimum number of single-character edits (insertions, deletions or substitutions) required to change one string to the other. Characters of different cases (e.g., a and A) are considered different.

  • like_escape()

    Returns true if the string matches the like_specifier (see Pattern Matching) using case-sensitive matching. escape_character is used to search for wildcard characters in the string.

  • lower()

    Converts string to lower case.

  • lpad()

    Pads the string with the character on the left until it has count characters. Truncates the string on the right if it has more than count characters.

  • ltrim()

    Removes any occurrences of any of the characters from the left side of the string. characters defaults to space.

  • md5()

    Returns the MD5 hash of the blob as a VARCHAR.

  • md5_number()

    Returns the MD5 hash of the blob as a HUGEINT.

  • md5_number_lower()
  • md5_number_upper()
  • nfc_normalize()

    Converts string to Unicode NFC normalized string. Useful for comparisons and ordering if text data is mixed between NFC normalized and not.

  • not_ilike_escape()

    Returns false if the string matches the like_specifier (see Pattern Matching) using case-insensitive matching. escape_character is used to search for wildcard characters in the string.

  • not_like_escape()

    Returns false if the string matches the like_specifier (see Pattern Matching) using case-sensitive matching. escape_character is used to search for wildcard characters in the string.

  • ord()

    Returns an INTEGER representing the unicode codepoint of the first character in the string.

  • overlay()

    Replaces a substring of string starting at character start with replacement. If count is not specified, it defaults to the length of replacement. Note that a start value of 1 refers to the first character of the string.

  • parse_dirname()

    Returns the top-level directory name from the given path. separator options: system, both_slash (default), forward_slash, backslash.

  • parse_dirpath()

    Returns the head of the path (the pathname until the last slash) similarly to Python's os.path.dirname. separator options: system, both_slash (default), forward_slash, backslash.

  • parse_filename()

    Returns the last component of the path similarly to Python's os.path.basename function. If trim_extension is true, the file extension will be removed (defaults to false). separator options: system, both_slash (default), forward_slash, backslash.

  • parse_formatted_bytes()

    Parses a human-readable representation of a size in bytes into an integer.

  • parse_path()

    Returns a list of the components (directories and filename) in the path similarly to Python's pathlib.parts function. separator options: system, both_slash (default), forward_slash, backslash.

  • path_join()

    Joins path components using the platform separator, normalizing duplicate separators and dot segments. Absolute RHS components must be compatible (same scheme/parent) or raise an error.

  • position()

    Returns location of first occurrence of search_string in string, counting from 1. Returns 0 if no match found.

  • prefix()

    Returns true if string starts with search_string.

  • printf()

    Formats a string using printf syntax.

  • regexp_escape()

    Escapes special patterns to turn string into a regular expression similarly to Python's re.escape function.

  • regexp_extract()

    If string contains the regex pattern, returns the capturing group specified by optional parameter group; otherwise, returns the empty string. The group must be a constant value. If no group is given, it defaults to 0. A set of optional regex options can be set.

  • regexp_extract_all()

    Finds non-overlapping occurrences of the regex in the string and returns the corresponding values of the capturing group. A set of optional regex options can be set.

  • regexp_full_match()

    Returns true if the entire string matches the regex. A set of optional regex options can be set.

  • regexp_matches()

    Returns true if string contains the regex, false otherwise. A set of optional regex options can be set.

  • regexp_replace()

    Clean strings by replacing text that matches a regular expression. Use the g option when every match should be replaced.

  • regexp_split_to_array()

    Splits the string along the regex. A set of optional regex options can be set.

  • regexp_split_to_table()
  • repeat()

    Repeats the blob count number of times.

  • repeat_row()
  • replace()

    Replaces any occurrences of the source with target in string.

  • replace_type()

    Casts all fields of type1 to type2

  • reverse()

    Reverses the string.

  • right()

    Extract the right-most count characters.

  • right_grapheme()

    Extracts the right-most count grapheme clusters.

  • rpad()

    Pads the string with the character on the right until it has count characters. Truncates the string on the right if it has more than count characters.

  • rtrim()

    Removes any occurrences of any of the characters from the right side of the string. characters defaults to space.

  • sha1()

    Returns a VARCHAR with the SHA-1 hash of the blob.

  • sha256()

    Returns a VARCHAR with the SHA-256 hash of the blob.

  • split()

    Splits the string along the separator.

  • split_part()
  • starts_with()

    Returns true if string begins with search_string.

  • str_split()

    Splits the string along the separator.

  • str_split_regex()

    Splits the string along the regex. A set of optional regex options can be set.

  • string_split()

    Splits the string along the separator.

  • string_split_regex()

    Splits the string along the regex. A set of optional regex options can be set.

  • string_to_array()

    Splits the string along the separator.

  • strip_accents()

    Strips accents from string.

  • strlen()

    Number of bytes in string.

  • strpos()

    Returns location of first occurrence of search_string in string, counting from 1. Returns 0 if no match found.

  • substr()

    Extracts substring starting from character start up to the end of the string. If optional argument length is set, extracts a substring of length characters instead. Note that a start value of 1 refers to the first character of the string.

  • substring()

    Extracts substring starting from character start up to the end of the string. If optional argument length is set, extracts a substring of length characters instead. Note that a start value of 1 refers to the first character of the string.

  • substring_grapheme()

    Extracts substring starting from grapheme clusters start up to the end of the string. If optional argument length is set, extracts a substring of length grapheme clusters instead. Note that a start value of 1 refers to the first character of the string.

  • suffix()

    Returns true if string ends with search_string.

  • to_base()

    Converts number to a string in the given base radix, optionally padding with leading zeros to min_length.

  • to_base64()

    Converts a blob to a base64 encoded string.

  • to_binary()

    Converts the string to binary representation.

  • to_hex()

    Converts blob to VARCHAR using hexadecimal encoding.

  • translate()

    Replaces each character in string that matches a character in the from set with the corresponding character in the to set. If from is longer than to, occurrences of the extra characters in from are deleted.

  • trim()

    Removes any occurrences of any of the characters from either side of the string. characters defaults to space.

  • ucase()

    Converts string to upper case.

  • unbin()

    Converts a value from binary representation to a blob.

  • unhex()

    Converts a value from hexadecimal representation to a blob.

  • unicode()

    Returns an INTEGER representing the unicode codepoint of the first character in the string.

  • upper()

    Converts string to upper case.

  • url_decode()

    Decodes a URL from a representation using Percent-Encoding.

  • url_encode()

    Encodes a URL to a representation using Percent-Encoding.