In Oracle, the LPAD() function allows you to pad the left part of a string with a certain character, to a specified number of characters.
The way it works is, you specify how long the resulting string should be. If the original string is shorter, the padding character fills in the remaining space.
Two similar functions in MariaDB are FIELD() and FIND_IN_SET(). At first glance, these appear to do the same thing, but there is a difference.
This article looks at the difference between these two functions.
In Oracle, the TRANSLATE() function allows you to make several single-character, one-to-one substitutions in one operation.
It returns the string provided as a first argument after some characters specified in the second argument are translated into a destination set of characters specified in the third argument.
It’s similar to the REPLACE() function, except that the REPLACE() function replaces the whole string with another string (i.e. not character by character, like TRANSLATE() does).
In Oracle, the NLSSORT() function returns a collation key for a given character value and an explicitly or implicitly specified collation.
The function can be useful for performing comparisons against string values based on a specified collation.
Ever since version 10.3.1, MariaDB has included both a LENGTH() function and a LENGTHB() function.
That second one has a B at the end of the name. So it’s kind of like Length A and Length B, except that Length A doesn’t have the A.
Confused?
I was, when I first encountered LENGTHB(). I already knew about LENGTH(), so why the need for a “B” version?
Let’s find out.
In Oracle, the SOUNDEX() function returns a character string containing the phonetic representation of its argument. This is known as the Soundex string.
Soundex is a phonetic algorithm for indexing names by sound, as pronounced in English. If two words sound the same, they should have the same Soundex string. If two words sound similar, but not exactly the same, their Soundex string might look similar but not exactly the same.
The function accepts one argument: the string for which to return the Soundex string from.
In Oracle, the SUBSTR() function returns a substring from a given string.
SUBSTR() requires at least two arguments; the string, and the position for which to extract the substring from. It also accepts an optional third argument that allows you to specify how long the substring should be.
SUBSTR() can also be thought of as a group of functions. There are five separate functions; SUBSTR(), SUBSTRB(), SUBSTRC(), SUBSTR2(), and SUBSTR4(). Each function calculates the length in a different way.
In Oracle, the CONCAT() function performs a string concatenation on its arguments. It returns the string that results from concatenating its arguments.
Concatenation is the operation of joining multiple strings end-to-end.
In Oracle, the REPLACE() function allows you to replace a part of a string with another string.
The function accepts three arguments: the string, the substring to replace, and the replacement string (to replace the substring with).
You can omit the replacement string, in which case, the function will remove all instances of the substring.
In Oracle, the LOWER() function returns its argument with all letters in lowercase.