Oracle:Character functions

Working with Character Values

This section looks at how to manipulate character data.

Character Expressions

You can combine character columns and constants into char expressions with the concatenation operator: || (two broken pipes). For example, to join the DNAME and LOC columns from the DEPT table into a single column, separating the two values with a blank space, a hyphen and another blank space:

SELECT  DNAME  ||  ' - '  || LOC  FROM  DEPT;

To join the ENAME and JOB columns from the EMP table, separating the two values with a space, colon(:) and another blank space and give the combined columns a new name:

SELECT ENAME || ' : ' || AS EMP_JOBS ;
FROM EMP;

Character Functions

SQL has many character functions. The following are the ones used most often. Col can represent either a column or character string.

INSTR(col, 'search-string')||INSTR(address,' ')||Returns the position of the first blank space in address

!Function	Example	Result
CONCAT(col1, col2)	CONCAT(CONCATforename, ' '), surname)	Returns col1 concatenated with col2. This function is equivalent to the concatenation operator (	). The example uses the operator twice to join the forename and surname with a space in between.
DECODE(col, 'value', new_value [,'value', new_value[,…]], default_value)	DECODE(GRADE,'A',14,'B',11,'C',8,'D',5,0)	Translates letter grades in the grade column into grade points. The last number represents the default value.
INITCAP(col)	INITCAP(surname)	Capitalises the first letter of each word in surname
LENGTH(col)	LENGTH(surname)	Returns the character length of surname
LOWER(col)	LOWER(surname)	Converts upper-case characters in surname to lower-case
LTRIM(col [,set])	LTRIM(surname)	Removes characters from the left of col, with all the leftmost characters that appear in set removed; set defaults to a single blank if omitted.
RTRIM(col [,set])	RTRIM(address)	Similar to LTRIM but removes characters on the right.
SOUNDEX(col)	SOUNDEX('Des Moines')	Returns a value that represents the spoken sound of 'Des Moines'
SUBSTR(col, m [, n])	SUBSTR(surname,1,2)	Returns part of the column, beginning at character m, for n characters long. This example gives the first two letters of surname. M can be positive, or negative. If positive the count starts from the beginning of the column, if negative, it counts backwards from the end of the column. If n is omitted, all characters to the end of the column are returned.
UPPER(col)	UPPER(surname)	Translates lower-case characters in surname to capital letters

Oracle commands are not case-sensitive, but when searching for character values the case is important. For instance, suppose you wish to display records from the EMP table, but cannot remember whether the employees’ names are in upper, lower or mixed case, if you try to search using lower case letters, when the data is in upper case you will not get any data returned. All is not lost, the UPPER, or LOWER functions can be used to ensure you compare like with like.

For example, to select and display an employee name in upper case and job capitalised:

SELECT  UPPER(ENAME),  INITCAP(JOB)  
FROM  EMP  
WHERE  LOWER(ENAME)  =  'ward';

Note, the value being compared in the WHERE clause should match the function, e.g., if the last line was changed to WHERE LOWER(ENAME) = 'WARD' it would retrieve any rows. Can you see why?

To select employees with names sounding like SMYTH:

SELECT ENAME 
FROM EMP 
WHERE SOUNDEX(ENAME) = SOUNDEX('SMYTH');

Note: both the attribute and string 'SMYTH' need to be converted to their phonetic values for this to work.