Wednesday, 1 March 2017

String, Conversion, and Miscellaneous functions

*  String functions
*  Conversion functions
*  Miscellaneous functions

In the last chapter we have seen how to use arithmetic and date functions. In this chapter let us see how to use string, conversion and miscellaneous functions.

String Functions
String functions are functions that manipulate a set of characters. A set of characters is a string. For example, the name of the company, the address of a person all these are examples of a string.  CHAR and VARCHAR data types contain strings.  Let us first see how to concatenate strings in Oracle.

Concatenating Strings
Two strings can be concatenated (added one after another) to form a single    string using the string concatenation operator, which is  ||  (two pipe symbols).

The following example concatenates name of the faculty with qualification. We also put two spaces between these two values to provide required space.

select name || '  ' ||  qual from faculty

George Koch  MS Computer Science
Dan Appleman  CS and EE graduate
Herbert Schildt  MS Computer Science
David Hunter  MS Electronics
Stephen Walther  Ph.D. in Philosophy
Kevin Loney  MS Eletronics
Jamie Jaworski  Bachlors of Electrical
Jason Couchman  OCP DBA

Table 1 shows the list of string functions.  These functions generally take a string as parameter and also return a string as return value.

LENGTH (string)
Returns the number of characters in the string.
LOWER (string)
Returns the string after converting the string to lowercase.
UPPER (string)
Returns the string after converting the string to uppercase.
INITCAP (string)
Converts first character of every word to uppercase and remaining to lower case.
LPAD (string, length [, fillstring])
Makes the string of the given length by padding the string on the left either with space or with fillstring.
RPAD (string, length [, fillstring])
Same as LPAD but pads on the right.
LTRIM (string [, charset])
Removes all left most characters of string up to the first character that is not in the charset.  if charset is not given then it defaults to blank.
RTRIM (string [, charset])
Same as LTRIM, but trims on the right.
TRIM (string)
Trims space on both sides.
SUBSTR (string, pos , length)
Extracts length number of characters from position pos in the string. If length is not given then extracts everything from pos.
INSTR (s1,s2 [,pos [,occurrence]])
Finds the starting position of s2 in s1. If occurrence is not given then it finds first occurrence. Search starts at pos, if given, otherwise at the first character in s1. 
ASCII (string)
Returns ASCII code of the first character in the given string
CHR (number)
Returns ASCII character for the given ASCII code.
TRANSLATE (string, from, to)
Replaces characters in from with to in string.
REPLACE (string, source, replace)
Replaces source in string with replace.
Table 1: String functions.

Converting Case
Functions LOWER and UPPER are straightforward. And they play a very important role in string comparison. As string comparison is case sensitive, LOWER or UPPER can be used to convert strings to uniform case before they are compared.

The following query tries to retrieve details of courses related to programming.

select name,duration from courses
where  name like '%programming%'

NAME                            DURATION
------------------------------ ---------
C programming                         20

The above query retrieves only one two whereas there are two rows that contain the word programming. It is because of the difference in the case. So the following query is converting the name to lowercase before comparison.

select name,duration from courses
where  LOWER(name) like '%programming%'

NAME                            DURATION
------------------------------ ---------
C programming                         20
XML Programming                       15

As NAME is converted to lowercase during comparison and compared with programming, which is in lowercase, the difference in case is ignored. The same result can be achieved even by using UPPER function. But in that case the string must be given in uppercase – PROGRAMMING.

INITCAP converts first letter of each word to capital and remaining letters to lowercase.

select initcap('this IS to Test INITCAP') Result
from  dual;

This Is To Test Initcap

INSTR function
INSTR returns the position in the first string where the second string starts in the first string.  If second string is not found in first string, it returns 0.

The default is to return the position of first occurrence by starting the search at the very first character in first string.  However, INSTR has options using which we can specify from where the search should start and which occurrence is to be considered.

The following examples illustrate the usage of  two optional  parameters; start and  occurrence.

select instr('How do you do','do') Postion
from dual;


Though string  “do” occurs for twice, the position of first occurrence is be returned. It is possible to specify to INSTR that it should start looking for do starting from the given position as follows.

select instr('How do you do','do',8) Postion
from dual


It is possible to specify that the position of the specified occurrence is to be returned as follows:

select instr('How do you do','do',1,2) Postion
from dual


Note: When occurrence is specified then starting position must also be specified, as third parameter cannot be omitted while fourth parameter is given.

The following example displays the details of courses where the letter p exists in the name of the course after 6th position.

select ccode,name
from courses
where  instr(name,'n') > 6

----- --------------------------
c     C programming
java  Java Language
xml   XML Programming

The same query can also be written as follows using LIKE operator and six underscores to indicate that first six letters may be anything but n must exists after that.  But INSTR version will be more flexible.

select ccode,name
from courses
where  name like '______%n%'

----- ------------------------------
c     C programming
java  Java Language
xml   XML Programming

We will se some more applications of INSTR at a later stage.

SUBSTR function
SUBSTR is used to extract a sub string from the given string. It takes the position from where extraction starts and the number of characters to be extracted.

The following example displays the first 2 character from the code of the course.

select  ccode, substr(ccode,1,2) sn from courses

----- --
ora   or
vbnet vb
c     c
asp   as
java  ja
xml   xm

It is possible to omit third parameter – length of the sub string. The following example illustrates it.

select  substr('Sateesh Technologies',8) Result from dual


The result of one function can be passed to another function as input. We have already seen nesting functions in the previous chapter.  Now let us see how we can combine SUBSTR and INSTR to get the first name of each faculty. First name is the name before space. 

select  name, substr( name, 1, instr(name,' ') - 1) Firstname from faculty;

NAME                           FIRSTNAME
------------------------------ ------------------------------
George Koch                    George
Dan Appleman                   Dan
Herbert Schildt                Herbert
David Hunter                   David
Stephen Walther                Stephen
Kevin Loney                    Kevin
Jamie Jaworski                 Jamie
Jason Couchman                 Jason

INSTR function is used to find out the position of first space. Then that position is used to specify the number of character to be taken from name. We subtracted one from position because position indicates the position of space but we have to take up to the character before the space.

The following is another example of these two functions. It take last name of the faculty.

select  name, substr(name,instr(name,' ') + 1) Lastname from faculty

NAME                           LASTNAME
------------------------------ ------------------------------
George Koch                    Koch
Dan Appleman                   Appleman
Herbert Schildt                Schildt
David Hunter                   Hunter
Stephen Walther                Walther
Kevin Loney                    Loney
Jamie Jaworski                 Jaworski
Jason Couchman                 Couchman

The following is the sequence of steps in the above query.

q  INSTR returns the position of first space in NAME
q  The return value of the INSTR, after 1 is added, is passed to SUBSTR as the starting position.
q  Everything on the right of the given position is taken by SUBSTR

Since Oracle converts the given values to required data type automatically, INSTR and SUBSTR can also be used with numbers and dates. The following query displays payments made in the month of April.

select * from payments
where  instr(dp,'APR') <> 0

   ROLLNO DP           AMOUNT
--------- --------- ---------
        9 07-APR-01      3000
       10 10-APR-01      4500
       11 07-APR-01      1000
       11 10-APR-01      3500

Trimming Strings
LTRIM and RTRIM are used to trim off unwanted characters from the left and right ends of the string respectively.  Leftmost spaces are called as leading spaces and rightmost spaces are called as trailing spaces.

They trim spaces by default. Optionally, you can specify which set of characters you want to trim.

The following example is used to trim spaces on the left using LEFT. The length of the string will show the result.

select length('  abc  xyz   ') Before,
       length(  ltrim('  abc  xyz   ')) After
from dual

--------- ---------
13            11

You can also trim a specified set of characters as shown below.

select ltrim( 'aabcbadxyabc','abc') Result from dual


In the above example, trimming stopped at ‘d’ because  ‘d ‘ is the first character that doesn’t fall in the character set of  ‘abc’.

While trimming, each character from left or right is taken and checked against the characters in the set. If character is same as any character in the character set, then character is trimmed otherwise trimming ends at that character. The same is true with RTRIM function, but it trims on the right.

TRIM function, which was introduced in Oracle8i, is used to trim both leading and trailing spaces.

select length('  abc  xyz  ') Before,
       length(trim('  abc  xyz  ')) After 
from dual

--------- ---------
       12         8

Padding Strings
A string can be made of a given length by padding either on the left using LPAD or on the right using RPAD. By default Oracle uses space to pad strings.  However, it is possible to specify which character(s) should be used for padding.

The following example course to 12 characters it specifies that dot is to be used for padding.

select rpad(name,12,'.') Name from courses

Oracle datab
C programmin
Java Languag
XML Programm

The above example is padding strings that are shorter than 12 characters and truncating strings that are larger than 12 characters.  Names like VB.NET and ASP.NET are padded on the right using dots. Whereas names like Oracle database and C programming are truncated to 12 characters.

Note: RPAD and LAPD truncate the given string if string has more number of characters than the given length.

These two functions return the string after modifying the given string. TRANSLATE works on individual characters, whereas REPLACE replaces a string with another string.

The following two examples will make the difference clear.

select replace('ABC ABAC XYZ DABC','ABC','PQR') Result from dual


REPLACE replaces every occurrence of string  ‘ABC’ with string ‘PRQ’.

select translate('ABC ABAC XYZ DABC','ABC','PQR') Result
from dual


TRANSLATE changes every occurrence of letter A with P, B with Q and C with R.

Conversion Functions
Conversion functions are used to convert a value from one data type into another. These functions are not required if Oracle can automatically convert the value. But there are cases where these conversion functions are required to convert the value to the required data type. The following table lists conversion functions.

TO_CHAR (value [, format])
Converts value, which is of DATE or NUMBER type, to CHAR type.
TO_DATE (char [, format])
Converts the given CHAR type value to DATE type.
TO_NUMBER (char)
Converts given CHAR type value to NUMBER type.
Table 2: Conversion Functions.

Before we understand how and where we use conversion functions, let us see how Oracle tries to convert the given data to the required data type.

Automatic Type Conversion
Oracle automatically converts the value to the required data type if it is possible. For example, if a number is used with string function, number is converted to string and then the function is executed.

In the same way, if a DATE type value is required but if a CHAR type value is given in the format DD-MON-YY or DD-MON-YYYY then Oracle converts it to DATE type.

But this automatic data type conversion is not always possible. To convert the value to the required data type, the given value must  already look like the data type it is being converted to. The following are guidelines that describe automatic  type conversion.


q  Any NUMBER or DATE will be converted to a CHAR.
q  If DATE is a literal enclose it in quotes.
q  CHAR type will be converted to NUMBER if it contains only digits, decimal point, or   minus sign on the left.
q  CHAR will be converted to DATE type if it is in DD-MON-YY or DD-MON-YYYY format.
q  A DATE will NOT be converted to NUMBER.
q  A NUMBER will NOT be converted to DATE.

The following few examples will give you better idea about automatic conversion of data type:

In the following example NUMBER is automatically converted to CHAR before LENGTH function is used.

select length(1133) from dual;


In the example below, a DATE given in CHAR format is converted to DATE before LAST_DAY function is applied.

select  last_day('20-aug-2001') from dual;


Similarly it is possible to use a CHAR value where a NUMBER is required, as shown below.

select 5 * '20' from dual;


Here are a few examples where Oracle cannot automatically convert the value.

SQL> select  next_day('12-1-2001', 'Fri') from dual;
select  next_day('12-1-2001', 'Fri') from dual
ERROR at line 1:
ORA-01843: not a valid month

Oracle returns an error saying the date is not having valid month because Oracle expects months to be of first three letters of the month name. As we have given only month number, it is not acceptable to Oracle. In this case we need to explicitly convert the value to DATE type using TO_DATE function.

The following sections will show how to use conversion functions.

TO_CHAR Function
This function is used to convert the given DATE or NUMBER to CHAR type.  TO_CHAR function may also be used to format the given date or number while converting the value to CHAR type. For example, to display date in DD-MM-YYYY format instead of standard format  - DD-MON-YY, enter the following:

select to_char(sysdate,'dd-mm-yyyy') Result from dual


In fact, TO_CHAR is one of the most frequently used functions. Here in the example, below it is used to display both date and time of SYSDATE. Remember this operation needs explicit usage of TO_CHAR as by default Oracle displays only date.

select to_char(sysdate,'dd Month yyyy hh24:mi:ss')
from  dual

15 August    2000 02:18:56

In the above example Month is standing for complete month name, yyyy stands for four digits year, hh24 for 24 hours based hour, mi minutes and ss for seconds.

Format in TO_CHAR function is a collection of more than 40 formatting options. Please see Table 3 for more options. For complete list, please see on-line help for Date Format Elements.

All options in the format are replaced with the corresponding values and remaining characters are returned as they are.  In the above example,  ‘:’ between HH24 and MI is returned as it is but HH24 and MI are replaced with the corresponding values.

Format Option
Number of the month: 10
First three letters of month name:  OCT
Complete month name:  OCTOBER
Day of the year since January 1st: 340
Day of the month: 16
Day of the week:  5
Day fully spelled:  Wednesday
Four digits year: 1996
Two digits year: 96
Year spelled out:  NINTEEN-NINTY-SIX
HH or HH12
Hour of the day:  5
Hour of the day: 20
Minute of hour: 30
Second of minute: 30
A.M.  or   P.M.
Displays A.M. or P.M. depending on the time.
Removes trailing spaces. ‘May          ‘ becomes  ‘May’
Suffix to number:  DDTH will produce 16th
Number Spelled out:  DDSP will produce THIRD for day 3.
Table 3: TO_CHAR and TO_DATE formats.

The following query retrieves details of the students who have joined in the month of April in year 2001.

select bcode, name from students
where  to_char(dj,'mmyyyy') = '042001';

----- ------------------------------
b5    Richard Marx
b5    Tina Turner
b5    Jody Foster

In the following example TO_CHAR is used to display month name of the year. However, as you can see in the output, there are trailing spaces after month name. This is because Oracle pads the name to 9 characters. Months that have smaller name than that will have trailing spaces.

select bcode, name, to_char(dj,'dd-Month-yyyy') dj from students

BCODE NAME                           DJ
----- ------------------------------ -----------------
b1    George Micheal                 10-January  -2001
b1    Micheal Douglas                11-January  -2001
b2    Andy Roberts                   11-January  -2001
b2    Malcom Marshall                16-January  -2001
b2    Vivan Richards                 16-January  -2001
b3    Chirs Evert                    14-January  -2001
b3    Ivan Lendal                    15-January  -2001
b4    George Micheal                 01-March    -2001
b5    Richard Marx                   06-April    -2001
b5    Tina Turner                    06-April    -2001
b5    Jody Foster                    07-April    -2001

Format fm can be used to remove these trailing spaces in months name. Here is revised version of the above query.

select bcode, name, to_char(dj,'dd-fmMonth-yyyy') dj from students

BCODE NAME                           DJ
----- ------------------------------ -----------------
b1    George Micheal                 10-January-2001
b1    Micheal Douglas                11-January-2001
b2    Andy Roberts                   11-January-2001
b2    Malcom Marshall                16-January-2001
b2    Vivan Richards                 16-January-2001
b3    Chirs Evert                    14-January-2001
b3    Ivan Lendal                    15-January-2001
b4    George Micheal                 01-March-2001
b5    Richard Marx                   06-April-2001
b5    Tina Turner                    06-April-2001
b5    Jody Foster                    07-April-2001

Note: The output of TO_CHAR will be in the same case as the format. For example, if Month is given then output will be April; if MONTH is given then output will be APRIL.

TO_DATE function
TO_DATE is used to convert a CHAR type value to DATE type.  If the value is in DD-MON-YY or DD-MM-YYYY format then TO_DATE is not needed because Oracle implicitly converts the value to DATE type.

When you insert a record with only date in DD-MON-YY format, time portion of the date is set to 00:00:00.  The following INSERT inserts a new row into PAYMETS table with date as well as time.

insert into payments
     values ( 10,to_date('14-04-2001 10:20:00',
                         'dd-mm-yyyy hh24:mi:ss'), 2000);

It is important to make sure the values given are matching with the format. That means, in the above example, as we gave dd-mm-yyyy hh24:mi:ss as the formation even the data is to given in the same format.  The format informs to Oracle how to interpret the given values. If there is any mismatch, the values may be misinterpreted.

The format options are same as TO_CHAR function format options. See Table 3 for available format options.

TO_NUMBER function
This function is required in only two occasions.  The following are the two cases.

q  To convert formatted number to number.
q  To sort CHAR data in numeric order.

The first application of TO_NUMBER is to convert formatted number to number. The following example is trying to multiply $333 by 20. But as the number with currency symbol is not taken as a number by Oracle, it results in error.

SQL> select  $333 * 20 from dual;
select  $333 * 20 from dual
ERROR at line 1:
ORA-00911: invalid character

TO_NUMBER function can be used to convert $333 to a number so that it is treated as a number by Oracle. The format in TO_NUMBER specified that the first character is to be taken as currency symbol and remaining as digits.

SQL> select to_number('$333','$999') * 20 from dual


Sorting strings in numeric order
Another usage of TO_NUMBER is to sort a column that contains numbers but stored in the form of CHAR type.

Assume the following data is existing in VNO column of VEHICLES table.  Column VNO is defined as VARCHAR2(10).

SQL> select vno from vehicles;


The following SELECT sorts the data but sorts the column VNO as a collection of strings. That means the numeric values are not taken into account and numbers are taken as a collection of characters (each digits is a character).

select vno from vehicles order by vno;


The output show that number 9 is at the bottom. It is because of the way strings are compared in sorting – first character first and then second character and so on.

To sort the data using numeric value, issue the following command where VNO column is converted to a number before it is sorted using TO_NUMBER function.

select vno from vehicles order by to_number(vno);


We will see a lot of usage of conversion function TO_CHAR throughout the rest of the book.

Miscellaneous Functions
Miscellaneous functions are the functions that can be used with any data type. See table 4 for the list of miscellaneous functions.

If expression is equivalent to first cond then first value is returned otherwise Oracle checks whether the expression is equivalent to second cond then second value is returned.  If expression doesn’t match with any of the values then elsevalue is returned.
Returns the greatest of the given values.
LEAST( value1, value2, ...)
Returns the least value of the given values.
Return value2 if value1 is null otherwise returns value1.
Table 4: Miscellaneous Functions.

The following examples will show you how to use miscellaneous functions.

DECODE  function
This function works like a multiple IF statement or a CASE/SWITCH statement in a typical programming language.

It takes a value and compares it with the given values one by one. Wherever the value is equivalent to the given value it returns the corresponding value.

The following example shows how to decode the GRADE of COURSE_FACULTY table.

select fcode, ccode, decode(grade,'A','Very Good',
                                  'C', 'Average',
                                  'Unknown') Grade
from course_faculty

----- ----- -------------
gk    ora   Very Good
kl    ora   Very Good
jc    ora   Very Good
da    vbnet Very Good
sw    asp   Very Good
da    asp   Good
hs    c     Very Good
dh    xml   Very Good
jj    java  Very Good
hs    java  Good
jj    c     Very Good
jj    vbnet Good

The function is used to display meaningful text for column GRADE, which contains only A,B or C.

The following example shows another usage of DECODE where we display the total remuneration paid to faculty. Assuming the payment is based on the time of the batch and no. of days of the batch.

select bcode,ccode,fcode, stdate, enddate,
decode(timing,1,200,2,150,175) * (enddate-stdate)  Amount from batches
where enddate is not null

----- ----- ----- --------- --------- ---------
b1    ora   gk    12-JAN-01 20-FEB-01      7800
b2    asp   da    15-JAN-01 05-MAR-01      7350
b3    c     hs    20-JAN-01 27-FEB-01      6650
b4    xml   dh    02-MAR-01 30-MAR-01      4900
b5    java  hs    05-APR-01 10-MAY-01      7000

GREATEST and LEAST functions
These functions take a collection of values and return a single value which is either the least or greatest of the given values as the case may be.

GREATEST is used to return the largest of the given values and LEAST the smallest of the given values.

The following example shows the discount to be given to each course. The scheme is to given discount of 10% on the course fee or 500 whichever is higher. The following query with GREATEST function will achieve the result.

select  ccode, name, greatest( fee * 0.10,500) Discount from courses;

CCODE NAME                            DISCOUNT
----- ------------------------------ ---------
ora   Oracle database                      500
vbnet VB.NET                               550
c     C programming                        500
asp   ASP.NET                              500
java  Java Language                        500
xml   XML Programming                      500

LEAST  Function can be used in the same manner but it sets the upper limit.  In the following query the discount is either 10% of the course fee or 500 whichever is lower.

select  ccode,name, least( fee * 0.10,500) Discount from courses

CCODE NAME                            DISCOUNT
----- ------------------------------ ---------
ora   Oracle database                      450
vbnet VB.NET                               500
c     C programming                        350
asp   ASP.NET                              500
java  Java Language                        450
xml   XML Programming                      400

Note:  GREATEST and LEAST will not treat string literal that is in date format as date. Instead these dates are taken as strings.

The following example shows how these two functions treat dates that are given in date format but as strings.

select  greatest ( '12-jun-2001','17-mar-2001')
from  dual


The above command returns  ‘17-mar-2001’ instead of  ‘12-jun-2001’ because when these two are treated as strings, value in second position in first string (7) is greater than it corresponding value in first string (2), so 17-mar-2001 is returned as the greatest value.

NVL function
It is used to return the second value if first value is null. This function has a lot of significance since Oracle returns a null value from any expression containing a null value.

Note: Any expression involving a null value will result in a null value.

The following query is to display the details of all batches.  But we get nothing – actually null value -  for NODAYS of batches b6 and b7 as they are have null value in ENDDATE. Since Oracle results in null value for any expression having a null value the result of  ENDDATE-STDATE is a null value.

select bcode, stdate, enddate - stdate nodays from batches;

----- --------- ---------
b1    12-JAN-01        39
b2    15-JAN-01        49
b3    20-JAN-01        38
b4    02-MAR-01        28
b5    05-APR-01        35
b6    12-JUL-01
b7    15-AUG-01

However, now we want to take ending date if batch is completed otherwise we want to take system date as ending date.

select bcode, stdate, nvl(enddate,sysdate) - stdate nodays from batches;

----- --------- ---------
b1    12-JAN-01        39
b2    15-JAN-01        49
b3    20-JAN-01        38
b4    02-MAR-01        28
b5    05-APR-01        35
b6    12-JUL-01  50.17985
b7    15-AUG-01  16.17985

Now we want to include even the status of the batch, which will be COMPLETED if ENDDATE is not null otherwise RUNNING.

select bcode, stdate, nvl(enddate,sysdate) - stdate nodays,
       decode(enddate,null,'Running','Completed') Status from batches;

----- --------- --------- ---------
b1    12-JAN-01        39 Completed
b2    15-JAN-01        49 Completed
b3    20-JAN-01        38 Completed
b4    02-MAR-01        28 Completed
b5    05-APR-01        35 Completed
b6    12-JUL-01   50.1811 Running
b7    15-AUG-01   16.1811 Running


String functions manipulate strings. Conversion functions are used to convert the data type of a value from one to another. In fact, Oracle always tries to convert the given value to the required data type. But in some cases as Oracle cannot convert implicitly, conversion functions are to be used to convert the value to the required data type.

Miscellaneous functions like DECODE and NVL can be used with any data type. DECODE is an if-elseif-else structure. NVL returns either the first value if it is not null or second value if first value is null.

SQL Interview Questions and Answers

There is given sql interview questions and answers that has been asked in many companies. For PL/SQL interview questions, visit our next...