Character Functions

Contact Us or call 1-877-932-8228
Character Functions

Character Functions

CONCAT

The CONCAT function is used to concatenate strings. The || operator can also be used to append one string to another.

LENGTH

The LENGTH function returns the number of characters (including spaces and hidden characters) in the string passed to it.

Code Sample:

SQL-Functions-Character/Demos/function_concat_length.sql
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).

INSTR

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

  • start the search for the string at a location other than the beginning
  • search backwards
  • search for a given occurence of the substring

REPLACE

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.

Code Sample:

SQL-Functions-Character/Demos/function_instr_replace.sql
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.

UPPER

The UPPER function is used to convert all of the character in a string to upper case.

LOWER

The LOWER function is used to convert all of the character in a string to lower case.

INITICAP

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.

Code Sample:

SQL-Functions-Character/Demos/function_upper_lower_initcap.sql
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

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

RPAD provides the same basic functionality as rpad, but performs right rather than left padding.

Code Sample:

SQL-Functions-Character/Demos/function_rpad_lpad.sql
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.

TRIM

The TRIM function is used to remove leading and/or trailing white space characters from a string.

Code Sample:

SQL-Functions-Character/Demos/function_trim.sql
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.

TO_CHAR

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.

Code Sample:

SQL-Functions-Character/Demos/function_to_char.sql
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

SOUNDEX

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".

Code Sample:

SQL-Functions-Character/Demos/function_soundex.sql
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".

Next