The CONCAT function is used to concatenate strings. The || operator can also be used to append one string to another.
The LENGTH function returns the number of characters (including spaces and hidden characters) in the string passed to it.
SELECT first_name, last_name, LENGTH(first_name), LENGTH(last_name) FROM employees WHERE LENGTH(first_name||last_name) IN ( SELECT MAX(LENGTH(first_name|| last_name)) FROM employees );
This query demonstrates the use of the concatenation operator (||) as well as the LENGTH function. It returns the rows with the longest combined first name and last name (including the length of both the first and last name for the record).
The INSTR function is used to determine if a given string contains a specified substring. The function returns an integer representing the position of the substring (if found). If the string is not found, zero is returned. There are variations of the function that can be used to
The REPLACE function is used to substitute one substring for another within a given text string. Oracle also includes a similar function called TRANSLATE that can be used to perform several single-character, one-to-one substitutions.
SELECT INSTR(first_name, 'an') AS position, REPLACE(first_name,'an','AN') AS first_name_with_an, first_name FROM employees WHERE INSTR(first_name, 'an') > 0;
This query returns all rows where the first name includes the substring "an" as part of the name. The columns in the SELECT clause reveal where the substring was found (its position), replaces the string with a capitalized version of the search string ("AN"), and also retrns the original first name field for comparison.
The UPPER function is used to convert all of the character in a string to upper case.
The LOWER function is used to convert all of the character in a string to lower case.
The INITCAP function is used to capitalize the first character in each substring that appears as an independent word in the string. It sets all remaining characters to lower case.
SELECT last_name, UPPER(last_name), LOWER(last_name), INITCAP(last_name) FROM employees WHERE last_name LIKE 'McC%';
This demo illustrates the effects of UPPER, LOWER and INITCAP on all rows that have last names that start with "McC".
LPAD is used to return a given string left-padded to a given length. The characters used for padding can be specified as well.
RPAD provides the same basic functionality as rpad, but performs right rather than left padding.
SELECT RPAD(first_name,7,'-') AS r, LPAD(first_name,7,'-') AS l, first_name FROM employees;
This query illustrates the effects of right and left padding names in the employee table to a length of 7 characters with dashes. Notice the effect of these functions on first names that are longer than seven characters.
The TRIM function is used to remove leading and/or trailing white space characters from a string.
SELECT LENGTH(txt) AS full_text, LENGTH(TRIM(txt)) AS trimmed_text FROM ( SELECT ' this has Extra spaces ' AS txt FROM dual );
This query shows the effects of stripping white space from a string USING the TRIM function. It uses the LENGTH function to illustrate the differences between the strings.
The TO_CHAR function is used to cast date, numeric or character data to a character type string. A variety of format models are available to change the appearance of numeric and date data.
SELECT first_name, last_name, TO_CHAR(salary,'$999,999.00') AS salary, TO_CHAR(hire_date, 'MM/DD/YYYY') AS hire_date FROM employees;
This query demonstrates how TO_CHAR can be used to structure numeric data as currency and date data using the MM/DD/YYYY format model. To see a list of formats available to go: TO_CHAR Functions
The SOUNDEX function returns a character string containing a phonetic representation that can be compared with other strings. Words that are spelled differently but sound alike (in English) will return the same value. The algorithm for this function was devised by Donald E. Knuth in his book "The Art of Computer Programming, Volume 3: Sorting and Searching".
SELECT first_name FROM employees WHERE SOUNDEX(first_name) = SOUNDEX('stephen');
This query returns rows where the first name of the employee sounds like "stephen".