Exam Topic: Using Single-Row Functions to Customize Output – Describe various types of functions available in SQL

Single row functions return a result for every row in a result. They can be used in any part of a SQL query, such as the SELECT lists or any part that can use a conditional statement. Oracle 11g has several built-in single row functions that work on most datatypes. Functions can take zero to many parameters as an input but it only returns one value, such as UPPER(‘test’) returns ‘TEST’. The following is a closer look at character functions that return character values. These include string manipulations.

Character Functions Returning Character Values

For all of these functions, the resulting data types are matched to the original in terms of unicode (the absence or presence of the n).

  • If the input argument is CHAR or VARCHAR2, then the value returned is VARCHAR2.
  • If the input argument is NCHAR or NVARCHAR2, then the value returned is NVARCHAR2.

Functions can only return their maximum length. Anything beyond their maximum return value is truncated.

CHR (n), NCHR(n) CHR Returns the number corresponding to the database character set. Can also be the national character set if you include USING NCHAR_CS. NCHR always returns the national character set.
CONCAT (char1,char2) Returns char1 concatenated with char2. Works with CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB and NCLOB. If there’s a national character set, then the result will be national character set.
REPLACE
(string,search,replacement)		 Used to replace sections of strings.
REPLACE('ABC','A','Z') becomes 'ZBC'.
REPLACE('ABC','A') becomes 'BC' because a replacement wasn’t specified.
LPAD (expr1, n, expr2)
RPAD (expr1, n, expr2)		 LPAD and RPAD work the same except that LPAD pads on the left and RPAD pads on the right. Expr1 is length 3 (‘ABC’) so it is being padded to n length (5) by expr2 (‘D’). Omitting expr2 pads with spaces.
LPAD('ABC',5,'D') becomes 'DDABC'.
RPAD('ABC',5,'D') becomes 'ABCDD'.
LTRIM, RTRIM, TRIM Trims remove characters. Any character can be specified.
trim(leading '-' from '--test--') becomes test--
trim(trailing '-' from '--test--') becomes–test
trim(both '-' from '--test--') or
trim('-' from '--test--') becomes test
By default, it removes white space.
'*' || ' --test--' || '*' becomes * --test-- * 
'*' || trim(' --test-- ') || '*' becomes *--test--*
LTRIM and RTRIM work like leading and trailing
'*' || ltrim(' --test-- ') || '*' becomes *--test-- *
'*' || rtrim(' --test-- ') || '*' becomes * --test--*
SUBSTR
(char, position [, length])		 Returns a portion of a string. char is the source string. position is the starting position. If position is null, then it starts at position 1, the start. length is optional. If it is null, substr returns everything from position to the end.
substr('TEST',1) returns TEST.
substr('TEST',2) returns EST.
substr('TEST',2,1) returns E.
INITCAP  Returns a string with each first letter capitalized.
initcap ('this is a test') returns This Is A Test
LOWER  Returns a string with all lowercase letters.
lower('THIS IS A TEST') returns this is a test
UPPER  Returns a string with all uppercase letters.
lower('this is a test') returns THIS IS A TEST
NLS_INITCAP  All three functions work the same as their non-NLS counterparts. The difference is that the NLS_ functions will allow for different sorts so that different character sets can be used.
NLS_INITCAP('ijsland', 'NLS_SORT = XDutch') returns IJsland.
NLS_LOWER ('CITTA''', 'NLS_SORT = XGerman') returns citta'
NLS_UPPER('große', 'NLS_SORT = XGerman') returns GROSSE
NLS_LOWER
NLS_UPPER
NLSSORT  Can be used in ORDER BY clauses when sorting by a different language.
SELECT * FROM test ORDER BY NLSSORT(name, 'NLS_SORT = XDanish');
NAME
---------------
Gaberd
Gaardiner
Gaasten
REGEXP_REPLACE  Works like REPLACE but allows searching the string for a regular expression pattern.
REGEXP_SUBSTR  Works like SUBSTR but allows searching the string for a regular expression pattern.
TRANSLATE  Used to replace one character for another. Each letter is replaced with corresponding character in to list. If there is nothing corresponding in the to list, it is replaced with an empty string or null. Using allows you to specify CHAR_CS or NCHAR_CS.
select translate ('testing out','st','ab') from dual;
ST_TO_AB
-----------
beabing oub
TRANSLATE…USING
SOUNDEX  Can be used to compare two words to see if they sound alike. The following returns 1.
select case when soundex('see')=soundex('sea') then 1 else 0 end from dual;
select case when soundex('hair')=soundex('hare') then 1 else 0 end from dual;
select case when soundex('smythe')=soundex('smith') then 1 else 0 end from dual;
Tagged: