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.

!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 INSTR(col, 'search-string')||INSTR(address,' ')||Returns the position of the first blank space in address	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

Suppose you wish to display records from the EMP table, but cannot remember whether the employees’ names are in upper, lower or mixed case. Which function is necessary to make sure you find the right records and display them correctly? UPPER or LOWER 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') ENAME

---

SMITH HINT: Both the attribute and string 'SMYTH' need to be converted to their phonetic values for this to work.