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.
trim()Remove whitespace at the edges.lower()Normalize letter case for matching.replace()Replace literal text.regexp_replace()Replace a pattern, once or globally.string_split()Split delimited text into a list.
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.
Guide example · Run to view results.
| first_match | all_matches | missing |
|---|---|---|
A# B34 | A# 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.
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 towidthcharacters whenx=max.widthdefaults to 80.base64()Converts a
blobto a base64 encoded string.bin()Converts the
stringto binary representation.bit_length()Returns the bit-length of the
bitargument.chr()Returns a character which is corresponding the ASCII code value or Unicode code point.
concat_ws()Concatenates many strings, separated by
separator.NULLinputs are skipped.contains()Returns
trueifsearch_stringis found withinstring.decode()Converts
blobtoVARCHAR. 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
stringtoBLOB. Converts UTF-8 characters into literal encoding.ends_with()Returns
trueifstringends withsearch_string.format()Formats a string using the fmt syntax.
format_bytes()Converts
integerto a human-readable representation using units based on powers of 2 (KiB, MiB, GiB, etc.).formatReadableDecimalSize()Converts
integerto a human-readable representation using units based on powers of 10 (KB, MB, GB, etc.).formatReadableSize()Converts
integerto a human-readable representation using units based on powers of 2 (KiB, MiB, GiB, etc.).from_base64()Converts a base64 encoded
stringto a character string (BLOB).from_binary()Converts a
valuefrom binary representation to a blob.from_hex()Converts a
valuefrom 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.,
aandA) are considered different.hash()Returns a
UBIGINTwith the hash of thevalue. Note that this is not a cryptographic hash.hex()Converts
blobtoVARCHARusing 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
trueif thestringmatches thelike_specifier(see Pattern Matching) using case-insensitive matching.escape_characteris used to search for wildcard characters in thestring.instr()Returns location of first occurrence of
search_stringinstring, counting from 1. Returns 0 if no match found.jaccard()The Jaccard similarity between two strings. Characters of different cases (e.g.,
aandA) are considered different. Returns a number between 0 and 1.jaro_similarity()The Jaro similarity between two strings. Characters of different cases (e.g.,
aandA) are considered different. Returns a number between 0 and 1. For similarity <score_cutoff, 0 is returned instead.score_cutoffdefaults to 0.jaro_winkler_similarity()The Jaro-Winkler similarity between two strings. Characters of different cases (e.g.,
aandA) are considered different. Returns a number between 0 and 1. For similarity <score_cutoff, 0 is returned instead.score_cutoffdefaults to 0.lcase()Converts
stringto 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.,
aandA) are considered different.like_escape()Returns
trueif thestringmatches thelike_specifier(see Pattern Matching) using case-sensitive matching.escape_characteris used to search for wildcard characters in thestring.lower()Converts
stringto lower case.lpad()Pads the
stringwith thecharacteron the left until it hascountcharacters. Truncates thestringon the right if it has more thancountcharacters.ltrim()Removes any occurrences of any of the
charactersfrom the left side of thestring.charactersdefaults tospace.md5()Returns the MD5 hash of the
blobas aVARCHAR.md5_number()Returns the MD5 hash of the
blobas aHUGEINT.md5_number_lower()md5_number_upper()nfc_normalize()Converts
stringto Unicode NFC normalized string. Useful for comparisons and ordering if text data is mixed between NFC normalized and not.not_ilike_escape()Returns
falseif thestringmatches thelike_specifier(see Pattern Matching) using case-insensitive matching.escape_characteris used to search for wildcard characters in thestring.not_like_escape()Returns
falseif thestringmatches thelike_specifier(see Pattern Matching) using case-sensitive matching.escape_characteris used to search for wildcard characters in thestring.ord()Returns an
INTEGERrepresenting theunicodecodepoint of the first character in thestring.overlay()Replaces a substring of
stringstarting at characterstartwithreplacement. Ifcountis not specified, it defaults to the length ofreplacement. Note that astartvalue of1refers to the first character of thestring.parse_dirname()Returns the top-level directory name from the given
path.separatoroptions:system,both_slash(default),forward_slash,backslash.parse_dirpath()Returns the head of the
path(the pathname until the last slash) similarly to Python'sos.path.dirname.separatoroptions:system,both_slash(default),forward_slash,backslash.parse_filename()Returns the last component of the
pathsimilarly to Python'sos.path.basenamefunction. Iftrim_extensionistrue, the file extension will be removed (defaults tofalse).separatoroptions: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
pathsimilarly to Python'spathlib.partsfunction.separatoroptions: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_stringinstring, counting from 1. Returns 0 if no match found.prefix()Returns
trueifstringstarts withsearch_string.printf()Formats a
stringusing printf syntax.regexp_escape()Escapes special patterns to turn
stringinto a regular expression similarly to Python'sre.escapefunction.regexp_extract()If
stringcontains theregexpattern, returns the capturing group specified by optional parametergroup; otherwise, returns the empty string. Thegroupmust be a constant value. If nogroupis given, it defaults to 0. A set of optional regexoptionscan be set.regexp_extract_all()Finds non-overlapping occurrences of the
regexin thestringand returns the corresponding values of the capturinggroup. A set of optional regexoptionscan be set.regexp_full_match()Returns
trueif the entirestringmatches theregex. A set of optional regexoptionscan be set.regexp_matches()Returns
trueifstringcontains theregex,falseotherwise. A set of optional regexoptionscan 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
stringalong theregex. A set of optional regexoptionscan be set.regexp_split_to_table()repeat()Repeats the
blobcountnumber of times.repeat_row()replace()Replaces any occurrences of the
sourcewithtargetinstring.replace_type()Casts all fields of type1 to type2
reverse()Reverses the
string.right()Extract the right-most
countcharacters.right_grapheme()Extracts the right-most
countgrapheme clusters.rpad()Pads the
stringwith thecharacteron the right until it hascountcharacters. Truncates thestringon the right if it has more thancountcharacters.rtrim()Removes any occurrences of any of the
charactersfrom the right side of thestring.charactersdefaults tospace.sha1()Returns a
VARCHARwith the SHA-1 hash of theblob.sha256()Returns a
VARCHARwith the SHA-256 hash of theblob.split()Splits the
stringalong theseparator.split_part()starts_with()Returns
trueifstringbegins withsearch_string.str_split()Splits the
stringalong theseparator.str_split_regex()Splits the
stringalong theregex. A set of optional regexoptionscan be set.string_split()Splits the
stringalong theseparator.string_split_regex()Splits the
stringalong theregex. A set of optional regexoptionscan be set.string_to_array()Splits the
stringalong theseparator.strip_accents()Strips accents from
string.strlen()Number of bytes in
string.strpos()Returns location of first occurrence of
search_stringinstring, counting from 1. Returns 0 if no match found.substr()Extracts substring starting from character
startup to the end of the string. If optional argumentlengthis set, extracts a substring oflengthcharacters instead. Note that astartvalue of1refers to the first character of thestring.substring()Extracts substring starting from character
startup to the end of the string. If optional argumentlengthis set, extracts a substring oflengthcharacters instead. Note that astartvalue of1refers to the first character of thestring.substring_grapheme()Extracts substring starting from grapheme clusters
startup to the end of the string. If optional argumentlengthis set, extracts a substring oflengthgrapheme clusters instead. Note that astartvalue of1refers to thefirstcharacter of thestring.suffix()Returns
trueifstringends withsearch_string.to_base()Converts
numberto a string in the given baseradix, optionally padding with leading zeros tomin_length.to_base64()Converts a
blobto a base64 encoded string.to_binary()Converts the
stringto binary representation.to_hex()Converts
blobtoVARCHARusing hexadecimal encoding.translate()Replaces each character in
stringthat matches a character in thefromset with the corresponding character in thetoset. Iffromis longer thanto, occurrences of the extra characters infromare deleted.trim()Removes any occurrences of any of the
charactersfrom either side of thestring.charactersdefaults tospace.ucase()Converts
stringto upper case.unbin()Converts a
valuefrom binary representation to a blob.unhex()Converts a
valuefrom hexadecimal representation to a blob.unicode()Returns an
INTEGERrepresenting theunicodecodepoint of the first character in thestring.upper()Converts
stringto upper case.url_decode()Decodes a URL from a representation using Percent-Encoding.
url_encode()Encodes a URL to a representation using Percent-Encoding.