Learning Objectives – after this chapter you will be able to:
- Concatenate strings with
||and use the string functions. - Combine INSTR and SUBSTR to extract parts of a string.
- Explain Oracle’s automatic type conversion rules.
- Use TO_CHAR, TO_DATE and TO_NUMBER with format models.
- Use DECODE, GREATEST, LEAST and NVL.
String Functions
Strings are stored in CHAR and VARCHAR2 columns. Two strings are joined with the concatenation operator ||: select name || ' ' || qual from faculty;
| Function | Description |
|---|---|
| LENGTH(string) | Number of characters |
| LOWER / UPPER(string) | Convert to lowercase / uppercase |
| INITCAP(string) | First letter of every word uppercase, rest lowercase |
| LPAD / RPAD(string, len [, fill]) | Pad on the left / right to len (default fill = space); truncates longer strings |
| LTRIM / RTRIM(string [, set]) | Remove characters in set (default space) from the left / right until a character not in the set |
| TRIM(string) | Remove leading and trailing spaces (Oracle8i) |
| SUBSTR(string, pos [, len]) | Extract len characters from pos (to the end if len is omitted) |
| INSTR(s1, s2 [, pos [, occurrence]]) | Starting position of s2 in s1; 0 if not found |
| ASCII(string) / CHR(number) | ASCII code of the first character / character for a code |
| TRANSLATE(string, from, to) | Character-by-character substitution |
| REPLACE(string, source, replace) | Replace every occurrence of a substring |
Case conversion, INSTR and SUBSTR
select name, duration from courses
where lower(name) like '%programming%'; -- case-insensitive search
select initcap('this IS to Test INITCAP') result from dual; -- This Is To Test Initcap
select instr('How do you do','do') from dual; -- 5 (first occurrence)
select instr('How do you do','do',8) from dual; -- 12 (search from position 8)
select instr('How do you do','do',1,2) from dual; -- 12 (2nd occurrence)
select substr('Srikanth Technologies',10) from dual; -- Technologies
select name, substr(name, 1, instr(name,' ') - 1) firstname from faculty;
select name, substr(name, instr(name,' ') + 1) lastname from faculty; When the occurrence argument of INSTR is given, the start position must also be given. Because Oracle converts types automatically, INSTR/SUBSTR also work on numbers and dates, e.g.
where instr(dp,'APR') <> 0.Trimming, padding, REPLACE vs TRANSLATE
select ltrim('aabcbadxyabc','abc') from dual; -- dxyabc
select rpad(name,12,'.') from courses; -- VB.NET...... / Oracle datab
select replace ('ABC ABAC XYZ DABC','ABC','PQR') from dual; -- PQR ABAC XYZ DPQR
select translate('ABC ABAC XYZ DABC','ABC','PQR') from dual; -- PQR PQPR XYZ DPQRConversion Functions
| Function | Converts |
|---|---|
| TO_CHAR(value [, format]) | DATE or NUMBER → CHAR (and formats it) |
| TO_DATE(char [, format]) | CHAR → DATE |
| TO_NUMBER(char [, format]) | CHAR → NUMBER |
Automatic type conversion – guidelines
- Any NUMBER or DATE is converted to CHAR (e.g.
LENGTH(1133)). - CHAR becomes NUMBER only if it contains digits, a decimal point or a leading minus sign (
5 * '20'= 100). - CHAR becomes DATE only in DD-MON-YY or DD-MON-YYYY format (
LAST_DAY('20-aug-2001')). - A DATE is never converted to NUMBER and a NUMBER is never converted to DATE.
NEXT_DAY('12-1-2001','Fri') fails with ORA-01843: not a valid month – the month must be given as letters, otherwise use TO_DATE with a format.
Format elements (TO_CHAR / TO_DATE)
| Element | Meaning | Element | Meaning |
|---|---|---|---|
| MM | Month number (10) | YYYY / YY | 4- / 2-digit year |
| MON | First 3 letters (OCT) | YEAR | Year spelled out |
| MONTH | Full month name | HH / HH12, HH24 | Hour (12 / 24-hour) |
| DDD / DD / D | Day of year / month / week | MI, SS | Minutes, seconds |
| Day | Day name (Wednesday) | A.M. / P.M. | Meridian indicator |
| fm | Removes padding spaces (‘May ’ → ‘May’) | TH / SP | 16th / spelled out (DDSP) |
TO_CHAR, TO_DATE and TO_NUMBER
select to_char(sysdate,'dd-mm-yyyy') from dual;
select to_char(sysdate,'dd Month yyyy hh24:mi:ss') from dual;
select bcode, name from students where to_char(dj,'mmyyyy') = '042001';
select bcode, name, to_char(dj,'dd-fmMonth-yyyy') dj from students; -- no padding
insert into payments
values (10, to_date('14-04-2001 10:20:00','dd-mm-yyyy hh24:mi:ss'), 2000);
select to_number('$333','$999') * 20 from dual; -- 6660
select vno from vehicles order by to_number(vno); -- numeric sort of a VARCHAR2 column- The case of TO_CHAR output follows the format: Month → April, MONTH → APRIL. Month names are padded to 9 characters unless fm is used.
- With TO_DATE the value must match the format exactly, otherwise it may be misinterpreted.
- TO_NUMBER is needed only to convert a formatted number (e.g. with a currency symbol –
$333 * 20gives ORA-00911) and to sort numeric strings numerically.
Miscellaneous Functions
| Function | Description |
|---|---|
| DECODE(expr, v1, r1, v2, r2, ..., else) | Works like a multiple IF / CASE: returns rn for the first matching vn, otherwise else |
| GREATEST(v1, v2, ...) | Largest value (sets a lower limit) |
| LEAST(v1, v2, ...) | Smallest value (sets an upper limit) |
| NVL(v1, v2) | v2 if v1 is NULL, otherwise v1 |
DECODE, GREATEST, LEAST and NVL
select fcode, ccode,
decode(grade,'A','Very Good','B','Good','C','Average','Unknown') grade
from course_faculty;
select ccode, greatest(fee * 0.10, 500) discount from courses; -- at least 500
select ccode, least (fee * 0.10, 500) discount from courses; -- at most 500
select bcode, nvl(enddate, sysdate) - stdate nodays,
decode(enddate, null, 'Running', 'Completed') status
from batches; Any expression involving NULL results in NULL – so ENDDATE − STDATE is NULL for running batches unless NVL supplies a value. GREATEST/LEAST treat date-looking literals as strings:
GREATEST('12-jun-2001','17-mar-2001') returns ‘17-mar-2001’.
Key Takeaways
- || concatenates; INSTR finds, SUBSTR extracts; TRANSLATE works per character, REPLACE per substring.
- Oracle converts types automatically when the value already “looks like” the target type.
- TO_CHAR formats dates and numbers; TO_DATE needs a matching format.
- DECODE is an if-elsif-else; NVL replaces NULL.
Chapter 7 Notes (PDF)
ch7.pdfKeys: ← → page · + − zoom · F full screen
Question / Answer (15 questions)
Moderate-level questions based on the Chapter 7 notes (PDF). Click a question to see the answer.
Answer : With the string concatenation operator || (two pipe symbols), e.g. SELECT name || ' ' || qual FROM faculty joins the faculty name, a space and the qualification into one string.
Answer : String comparison is case sensitive. Converting the column to a uniform case before comparing ignores case differences, e.g. WHERE LOWER(name) LIKE '%programming%' finds both "C programming" and "XML Programming".
Answer : INITCAP converts the first character of every word to uppercase and the remaining characters to lowercase, e.g. INITCAP('this IS to Test INITCAP') returns "This Is To Test Initcap".
Answer : INSTR(s1, s2 [, pos [, occurrence]]) returns the position where s2 starts in s1, or 0 if not found. By default it returns the first occurrence searching from the first character: INSTR('How do you do','do') is 5. INSTR('How do you do','do',8) starts at position 8 and returns 12, and INSTR('How do you do','do',1,2) returns the second occurrence, 12. When occurrence is given, the start position must also be given.
Answer : First name: SUBSTR(name, 1, INSTR(name, ' ') - 1) – one is subtracted because INSTR gives the position of the space. Last name: SUBSTR(name, INSTR(name, ' ') + 1) – everything after the first space.
Answer : Each character from the left is checked against the characters in the set; if it is in the set it is trimmed, otherwise trimming stops. In the example trimming stops at "d", the first character not in "abc", so the result is "dxyabc". RTRIM works the same way on the right; without a set both trim spaces.
Answer : They pad shorter strings to the given length with spaces or the given fill string, but truncate strings longer than the length. For example, RPAD(name,12,'.') gives "VB.NET......" and "Oracle datab".
Answer : TRANSLATE works on individual characters: TRANSLATE('ABC ABAC XYZ DABC','ABC','PQR') changes every A to P, B to Q and C to R, giving "PQR PQPR XYZ DPQR". REPLACE replaces a whole substring: REPLACE('ABC ABAC XYZ DABC','ABC','PQR') gives "PQR ABAC XYZ DPQR".
Answer : Any NUMBER or DATE is converted to CHAR; CHAR is converted to NUMBER if it contains only digits, a decimal point or a minus sign on the left; CHAR is converted to DATE if it is in DD-MON-YY or DD-MON-YYYY format; a DATE is not converted to NUMBER and a NUMBER is not converted to DATE.
Answer : It gives ORA-01843: not a valid month because Oracle expects the month as the first three letters of its name for automatic conversion. The value must be converted explicitly with TO_DATE and a matching format, e.g. TO_DATE('12-1-2001','dd-mm-yyyy').
Answer : TO_CHAR(SYSDATE,'dd Month yyyy hh24:mi:ss') displays both date and time. dd is day of month, Month the complete month name, yyyy the four-digit year, hh24 the 24-hour based hour, mi minutes and ss seconds; other characters such as ":" are returned as they are.
Answer : Oracle pads month names to 9 characters, leaving trailing spaces after shorter names. fm removes these spaces, e.g. TO_CHAR(dj,'dd-fmMonth-yyyy') displays "06-April-2001" instead of "06-April -2001".
Answer : To convert a formatted number to a number, e.g. TO_NUMBER('$333','$999') * 20 = 6660 (while $333 * 20 gives ORA-00911 invalid character), and to sort CHAR data that contains numbers in numeric order, e.g. ORDER BY TO_NUMBER(vno).
Answer : DECODE(expression, cond1, value1, cond2, value2, ..., elsevalue) works like a multiple IF or CASE statement: it returns the value of the first matching condition, otherwise elsevalue. Example: DECODE(grade,'A','Very Good','B','Good','C','Average','Unknown').
Answer : Any expression involving a null value results in null, so AMOUNT - DISCOUNT is null when DISCOUNT is null. NVL(value1, value2) returns value2 when value1 is null, e.g. NVL(enddate, SYSDATE) - stdate gives the number of days even for running batches; AMOUNT - NVL(DISCOUNT,0) gives the amount.
MCQ Test : Check Your Understanding (5 questions)
Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.
Bibliography/References
The chapter notes PDF is the course study material and is credited to its author below. The explanations, questions and MCQs on this page are original study aids written for this course from the topics covered in that PDF. The other works are cited for verification and further reading. Oracle, SQL*Plus and PL/SQL are trademarks of Oracle Corporation; other names belong to their respective owners.
- Srikanth, P. (n.d.). Oracle For Beginners (chapter notes – the PDF shown on each chapter page). Srikanth Technologies. http://www.srikanthtechnologies.com/
- Oracle Corporation. (n.d.). Oracle Database SQL Language Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/
- Oracle Corporation. (n.d.). Oracle Database Concepts, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/
- Feuerstein, S., & Pribyl, B. (2014). Oracle PL/SQL Programming (6th ed.). O’Reilly Media.