Oracle:Character functions
1 WORKING WITH CHARACTER VALUES 1.1 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;
EMP_JOBS
KING : PRESIDENT JONES : MANAGER SCOTT : ANALYST ADAMS : CLERK FORD : ANALYST SMITH : CLERK BLAKE : MANAGER ALLEN : SALESMAN WARD : SALESMAN MARTIN : SALESMAN TURNER : SALESMAN JAMES : CLERK CLARK : MANAGER MILLER : CLERK WILSON : CLERK GARVEY : MANAGER MASON : ANALYST FOX : ANALYST 18 rows selected. 1.2 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(CONCAT (forename, ' '), 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.
