ORACLE/PLSQL: UPPER FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL UPPER function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL UPPER function converts all letters in the specified string to uppercase. If there are characters in the string that are not letters, they are unaffected by this function.

SYNTAX

The syntax for the Oracle/PLSQL UPPER function is:

UPPER( string1 )

Parameters or Arguments

string1 is the string to convert to uppercase.

NOTE

See also the LOWER function.

APPLIES TO

The UPPER function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle UPPER function examples and explore how to use the UPPER function in Oracle/PLSQL.

For example:

UPPER('Tech on the Net')
Result: 'TECH ON THE NET'

UPPER('george burns 123   ')
Result: 'GEORGE BURNS 123   '

FREQUENTLY ASKED QUESTIONS

Question: How do you incorporate the Oracle UPPER function with the LIKE condition? I'm trying to query against a free text field for all records containing the word "test". The problem is that it can be entered in the following ways: TEST, Test, or test.

Answer: To answer this question, let's look at an example.

Let's say that we have a suppliers table with a field called supplier_name that contains the values TEST, Test, or test.

If we wanted to find all records containing the word "test", regardless of whether it was stored as TEST, Test, or test, we could run either of the following SQL statements:

select * from suppliers
where UPPER(supplier_name) like ('TEST%');

select * from suppliers
where UPPER(supplier_name) like UPPER('test%')

These SQL statements use a combination of the UPPER function and the LIKE condition to return all of the records where thesupplier_name field contains the word "test", regardless of whether it was stored as TEST, Test, or test.

ORACLE/PLSQL: INITCAP FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL INITCAP function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL INITCAP function sets the first character in each word to uppercase and the rest to lowercase.

SYNTAX

The syntax for the Oracle/PLSQL INITCAP function is:

INITCAP( string1 )

Parameters or Arguments

string1 is the string argument whose first character in each word will be converted to uppercase and all remaining characters converted to lowercase.

APPLIES TO

The INITCAP function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle INITCAP function examples and explore how to use the INITCAP function in Oracle/PLSQL.

For example:

INITCAP('tech on the net');
Result: 'Tech On The Net'

INITCAP('GEORGE BURNS');
Result: 'George Burns'

ORACLE/PLSQL: LTRIM FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL LTRIM function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL LTRIM function removes all specified characters from the left-hand side of a string.

SYNTAX

The syntax for the Oracle/PLSQL LTRIM function is:

LTRIM( string1, [ trim_string ] )

Parameters or Arguments

string1 is the string to trim the characters from the left-hand side.

trim_string is the string that will be removed from the left-hand side of string1. If this parameter is omitted, the LTRIM function will remove all leading spaces from string1.

NOTE

See also the RTRIM and TRIM functions.

APPLIES TO

The LTRIM function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle LTRIM function examples and explore how to use the LTRIM function in Oracle/PLSQL.

For example:

LTRIM('   tech')
Result: 'tech'

LTRIM('   tech', ' ')
Result: 'tech'

LTRIM('000123', '0')
Result: '123'

LTRIM('123123Tech', '123')
Result: 'Tech'

LTRIM('123123Tech123', '123')
Result: 'Tech123'

LTRIM('xyxzyyyTech', 'xyz')
Result: 'Tech'

LTRIM('6372Tech', '0123456789')
Result: 'Tech'

The LTRIM function may appear to remove patterns, but this is not the case as demonstrated in the following example.

LTRIM('xxyyxzyxyyxTech', 'xyz')
Result: 'Tech'

It actually removes the individual occurrences of 'x', 'y', and 'z', as opposed to the pattern of 'xyz'.

The LTRIM function can also be used to remove all leading numbers as demonstrated in the next example.

LTRIM( '637Tech', '0123456789')
Result: 'Tech'

In this example, every number combination from 0 to 9 has been listed in the trim_string parameter. By doing this, it does not matter the order that the numbers appear in string1, all leading numbers will be removed by the LTRIM function.

ORACLE/PLSQL: RTRIM FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL RTRIM function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL RTRIM function removes all specified characters from the right-hand side of a string.

SYNTAX

The syntax for the Oracle/PLSQL RTRIM function is:

RTRIM( string1, [ trim_string ] )

Parameters or Arguments

string1 is the string to trim the characters from the right-hand side.

trim_string is the string that will be removed from the right-hand side of string1. If this parameter is omitted, the RTRIM function will remove all trailing spaces from string1.

NOTE

See also the LTRIM and TRIM functions.

APPLIES TO

The RTRIM function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle RTRIM function examples and explore how to use the RTRIM function in Oracle/PLSQL.

For example:

RTRIM('tech   ')
Result: 'tech'

RTRIM('tech   ', ' ')
Result: 'tech'

RTRIM('123000', '0')
Result: '123'

RTRIM('Tech123123', '123')
Result: 'Tech'

RTRIM('123Tech123', '123')
Result: '123Tech'

RTRIM('Techxyxzyyy', 'xyz')
Result: 'Tech'

RTRIM('Tech6372', '0123456789')
Result: 'Tech'

The RTRIM function may appear to remove patterns, but this is not the case as demonstrated in the following example.

RTRIM('Techxyxxyzyyyxx', 'xyz')
Result: 'Tech'

It actually removes the individual occurrences of 'x', 'y', and 'z', as opposed to the pattern of 'xyz'.

The RTRIM function can also be used to remove all trailing numbers as demonstrated in the next example.

RTRIM('Tech6372', '0123456789')
Result: 'Tech'

In this example, every number combination from 0 to 9 has been listed in the trim_string parameter. By doing this, it does not matter the order that the numbers appear in string1, all trailing numbers will be removed by the RTRIM function.

ORACLE/PLSQL: TRIM FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL TRIM function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL TRIM function removes all specified characters either from the beginning or the ending of a string.

SYNTAX

The syntax for the Oracle/PLSQL TRIM function is:

TRIM( [ [ LEADING | TRAILING | BOTH ] trim_character FROM ] string1 )

Parameters or Arguments

LEADING - remove trim_character from the front of string1.

TRAILING - remove trim_character from the end of string1.

BOTH - remove trim_character from the front and end of string1.

trim_character is the character that will be removed from string1. If this parameter is omitted, the TRIM function will remove space characters from string1.

string1 is the string to trim.

NOTE

If you do not choose a value for the first parameter (LEADING, TRAILING, BOTH), the TRIM function will removetrim_character from both the front and end of string1.
See also the LTRIM and RTRIM functions.

APPLIES TO

The TRIM function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle TRIM function examples and explore how to use the TRIM function in Oracle/PLSQL.

For example:

TRIM('   tech   ')
Result: 'tech'

TRIM(' '  FROM  '   tech   ')
Result: 'tech'

TRIM(LEADING '0' FROM '000123')
Result: '123'

TRIM(TRAILING '1' FROM 'Tech1')
Result: 'Tech'

TRIM(BOTH '1' FROM '123Tech111')
Result: '23Tech'

ORACLE/PLSQL: SUBSTR FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL SUBSTR function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL SUBSTR functions allows you to extract a substring from a string.

SYNTAX

The syntax for the Oracle/PLSQL SUBSTR function is:

SUBSTR( string, start_position, [ length ] )

Parameters or Arguments

string is the source string.

start_position is the position for extraction. The first position in the string is always 1.

length is optional. It is the number of characters to extract. If this parameter is omitted, the SUBSTR function will return the entire string.

NOTE

If start_position is 0, then the SUBSTR function treats start_position as 1 (ie: the first position in the string).
If start_position is a positive number, then the SUBSTR function starts from the beginning of the string.
If start_position is a negative number, then the SUBSTR function starts from the end of the string and counts backwards.
If length is a negative number, then the SUBSTR function will return a NULL value.

APPLIES TO

The SUBSTR function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle SUBSTR function examples and explore how to use the SUBSTR function in Oracle/PLSQL.

For example:

SUBSTR('This is a test', 6, 2)
Result: 'is'

SUBSTR('This is a test', 6)
Result: 'is a test'

SUBSTR('TechOnTheNet', 1, 4)
Result: 'Tech'

SUBSTR('TechOnTheNet', -3, 3)
Result: 'Net'

SUBSTR('TechOnTheNet', -6, 3)
Result: 'The'

SUBSTR('TechOnTheNet', -8, 2)
Result: 'On'

ORACLE/PLSQL: REPLACE FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL REPLACE function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL REPLACE function replaces a sequence of characters in a string with another set of characters.

SYNTAX

The syntax for the Oracle/PLSQL REPLACE function is:

REPLACE( string1, string_to_replace, [ replacement_string ] )

Parameters or Arguments

string1 is the string to replace a sequence of characters with another set of characters.

string_to_replace is the string that will be searched for in string1.

replacement_string is optional. All occurrences of string_to_replace will be replaced with replacement_string in string1. If thereplacement_string parameter is omitted, the REPLACE function simply removes all occurrences of string_to_replace, and returns the resulting string.

APPLIES TO

The REPLACE function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle REPLACE function examples and explore how to use the REPLACE function in Oracle/PLSQL.

For example:

REPLACE('123123tech', '123');
Result: 'tech'

REPLACE('123tech123', '123');
Result:'tech'

REPLACE('222tech', '2', '3');
Result: '333tech'

REPLACE('0000123', '0');
Result: '123'

REPLACE('0000123', '0', ' ');
Result: '    123'

ORACLE/PLSQL: INSTR FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL INSTR function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL INSTR function returns the location of a substring in a string.

SYNTAX

The syntax for the Oracle/PLSQL INSTR function is:

INSTR( string, substring [, start_position [,  nth_appearance ] ] )

Parameters or Arguments

string is the string to search. string can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

substring is the substring to search for in string. substring can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

start_position is the position in string where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the INSTR function counts back start_position number of characters from the end of string and then searches towards the beginning of string.

nth_appearance is the nth appearance of substring. This is optional. If omitted, it defaults to 1.

NOTE

If substring is not found in string, then the INSTR function will return 0.

APPLIES TO

The INSTR function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle INSTR function examples and explore how to use the INSTR function in Oracle/PLSQL.

For example:

INSTR('Tech on the net', 'e')
Result: 2   (the first occurrence of 'e')

INSTR('Tech on the net', 'e', 1, 1)
Result: 2   (the first occurrence of 'e')

INSTR('Tech on the net', 'e', 1, 2)
Result: 11  (the second occurrence of 'e')

INSTR('Tech on the net', 'e', 1, 3)
Result: 14  (the third occurrence of 'e')

INSTR('Tech on the net', 'e', -3, 2)
Result: 2

ORACLE/PLSQL: CASE STATEMENT

This Oracle tutorial explains how to use the Oracle/PLSQL CASE statement with syntax and examples.

DESCRIPTION

The Oracle/PLSQL CASE statement has the functionality of an IF-THEN-ELSE statement. Starting in Oracle 9i, you can use the CASE statement within a SQL statement.

SYNTAX

The syntax for the Oracle/PLSQL CASE statement is:

CASE [ expression ]

   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n

   ELSE result

END

Parameters or Arguments

expression is optional. It is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)

condition_1 to condition_n must all be the same datatype. Conditions are evaluated in the order listed. Once a condition is found to be true, the CASE statement will return the result and not evaluate the conditions any further.

result_1 to result_n must all be the same datatype. This is the value returned once a condition is found to be true.

NOTE

If no condition is found to be true, then the CASE statement will return the value in the ELSE clause.

If the ELSE clause is omitted and no condition is found to be true, then the CASE statement will return NULL.

You can have up to 255 comparisons in a CASE statement. Each WHEN ... THEN clause is considered 2 comparisons.

APPLIES TO

The CASE statement can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

The CASE statement can be used in Oracle/PLSQL.

You could use the CASE statement in a SQL statement as follows: (includes the expression clause)

SELECT table_name,
CASE owner
  WHEN 'SYS' THEN 'The owner is SYS'
  WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
  ELSE 'The owner is another value'
END
FROM all_tables;

Or you could write the SQL statement using the CASE statement like this: (omits the expression clause)

SELECT table_name,
CASE
  WHEN owner='SYS' THEN 'The owner is SYS'
  WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
  ELSE 'The owner is another value'
END
FROM all_tables;

The above two CASE statements are equivalent to the following IF-THEN-ELSE statement:

IF owner = 'SYS' THEN
   result := 'The owner is SYS';

ELSIF owner = 'SYSTEM' THEN
   result := 'The owner is SYSTEM'';

ELSE
   result := 'The owner is another value';

END IF;

The CASE statement will compare each owner value, one by one.

One thing to note is that the ELSE clause within the CASE statement is optional. You could have omitted it. Let's look at the SQL statement above with the ELSE clause omitted.

Your SQL statement would look as follows:

SELECT table_name,
CASE owner
  WHEN 'SYS' THEN 'The owner is SYS'
  WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
END
FROM all_tables;

With the ELSE clause omitted, if no condition was found to be true, the CASE statement would return NULL.

Comparing 2 Conditions

Here is an example that demonstrates how to use the CASE statement to compare different conditions:

SELECT
CASE
  WHEN a < b THEN 'hello'
  WHEN d < e THEN 'goodbye'
END
FROM suppliers;

FREQUENTLY ASKED QUESTIONS

Question: Can you create a CASE statement that evaluates two different fields? I want to return a value based on the combinations in two different fields.

Answer: Yes, below is an example of a case statement that evaluates two different fields.

SELECT supplier_id,
CASE
  WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'North office'
  WHEN supplier_name = 'IBM' and supplier_type = 'Software' THEN 'South office'
END
FROM suppliers;

So if supplier_name field is IBM and the supplier_type field is Hardware, then the CASE statement will return North office. If the supplier_name field is IBM and the supplier_type is Software, the CASE statement will return South office.

ORACLE/PLSQL: LPAD FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL LPAD function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL LPAD function pads the left-side of a string with a specific set of characters (when string1 is not null).

SYNTAX

The syntax for the Oracle/PLSQL LPAD function is:

LPAD( string1, padded_length, [ pad_string ] )

Parameters or Arguments

string1 is the string to pad characters to (the left-hand side).

padded_length is the number of characters to return. If the padded_length is smaller than the original string, the LPAD function will truncate the string to the size of padded_length.

pad_string is optional. This is the string that will be padded to the left-hand side of string1. If this parameter is omitted, the LPAD function will pad spaces to the left-side of string1.

APPLIES TO

The LPAD function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle LPAD function examples and explore how to use the LPAD function in Oracle/PLSQL.

For example:

LPAD('tech', 7);
Result: '   tech'

LPAD('tech', 2);
Result: 'te'

LPAD('tech', 8, '0');
Result: '0000tech'

LPAD('tech on the net', 15, 'z');
Result: 'tech on the net'

LPAD('tech on the net', 16, 'z');
Result: 'ztech on the net'

ORACLE/PLSQL: RPAD FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL RPAD function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL RPAD function pads the right-side of a string with a specific set of characters (when string1 is not null).

SYNTAX

The syntax for the Oracle/PLSQL RPAD function is:

RPAD( string1, padded_length, [ pad_string ] )

Parameters or Arguments

string1 is the string to pad characters to (the right-hand side).

padded_length is the number of characters to return. If the padded_length is smaller than the original string, the RPAD function will truncate the string to the size of padded_length.

pad_string is optional. This is the string that will be padded to the right-hand side of string1. If this parameter is omitted, the RPAD function will pad spaces to the right-side of string1.

APPLIES TO

The RPAD function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle RPAD function examples and explore how to use the RPAD function in Oracle/PLSQL.

For example:

RPAD('tech', 7)
Result: 'tech   '

RPAD('tech', 2)
Result: 'te'

RPAD('tech', 8, '0')
Result: 'tech0000'

RPAD('tech on the net', 15, 'z')
Result: 'tech on the net'

RPAD('tech on the net', 16, 'z')
Result: 'tech on the netz'

ORACLE/PLSQL: COALESCE FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL COALESCE function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL COALESCE function returns the first non-null expression in the list. If all expressions evaluate to null, then the COALESCE function will return null.

SYNTAX

The syntax for the Oracle/PLSQL COALESCE function is:

COALESCE( expr1, expr2, ... expr_n )

Parameters or Arguments

expr1 to expr_n are the expressions to test for non-null values.

APPLIES TO

The COALESCE function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

The COALESCE function can be used in Oracle/PLSQL.

You could use the coalesce function in a SQL statement as follows:

SELECT COALESCE( address1, address2, address3 ) result
FROM suppliers;

The above COALESCE function is equivalent to the following IF-THEN-ELSE statement:

IF address1 is not null THEN
   result := address1;

ELSIF address2 is not null THEN
   result := address2;

ELSIF address3 is not null THEN
   result := address3;

ELSE
   result := null;

END IF;

The COALESCE function will compare each value, one by one.

ORACLE/PLSQL: ASCII FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL ASCII function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL ASCII function returns the NUMBER code that represents the specified character.

SYNTAX

The syntax for the Oracle/PLSQL ASCII function is:

ASCII( single_character )

Parameters or Arguments

single_character is the specified character to retrieve the NUMBER code for. If more than one character is entered, the ASCII function will return the value for the first character and ignore all of the characters after the first.

APPLIES TO

The ASCII function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle ASCII function examples and explore how to use the ASCII function in Oracle/PLSQL.

For example:

ASCII('t')
Result: 116

ASCII('T')
Result: 84

ASCII('T2')
Result: 84

View a listing of the ASCII values.

ORACLE/PLSQL: ASCIISTR FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL ASCIISTR function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL ASCIISTR function converts a string in any character set to an ASCII string using the database character set.

SYNTAX

The syntax for the Oracle/PLSQL ASCIISTR function is:

ASCIISTR( string )

Parameters or Arguments

string is a string in any character set that you want converted to an ASCII string in the database character set.

APPLIES TO

The ASCIISTR function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

Let's look at some Oracle ASCIISTR function examples and explore how to use the ASCIISTR function in Oracle/PLSQL.

For example:

ASCIISTR('A B C Ä Ê')
Result: 'A B C \00C4 \00CA'

ASCIISTR('A B C Õ Ø')
Result: 'A B C \00D5 \00D8'

ASCIISTR('A B C Ä Ê Í Õ Ø')
Result: 'A B C \00C4 \00CA \00CD \00D5 \00D8'

ORACLE/PLSQL: CHR FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL CHR function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL CHR function is the opposite of the ASCII function. It returns the character based on the NUMBER code.

SYNTAX

The syntax for the Oracle/PLSQL CHR function is:

CHR( number_code )

Parameters or Arguments

number_code is the NUMBER code used to retrieve the character.

APPLIES TO

The CHR function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle CHR function examples and explore how to use the CHR function in Oracle/PLSQL.

For example:

CHR(116)
Result: 't'

CHR(84)
Result: 'T'

View a listing of the ASCII values.

ORACLE/PLSQL: NCHR FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL NCHR function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL NCHR function returns the character based on the number_code in the national character set.

SYNTAX

The syntax for the Oracle/PLSQL NCHR function is:

NCHR( number_code )

Parameters or Arguments

number_code is the NUMBER code in the national character set used to retrieve the character.

NOTE

NCHR( number_code ) is the same as using the CHR function with the USING NCHAR_CS clause as follows:

CHR ( number_code USING NCHAR_CS )

APPLIES TO

The NCHR function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle NCHR function examples and explore how to use the NCHR function in Oracle/PLSQL.

For example:

NCHR(116)
Result: 't'

NCHR(84)
Result: 'T'

View a listing of the ASCII values.

ORACLE/PLSQL: COMPOSE FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL COMPOSE function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL COMPOSE function returns a Unicode string.

SYNTAX

The syntax for the Oracle/PLSQL COMPOSE function is:

COMPOSE( string )

Parameters or Arguments

string is the input value used to create the Unicode string. It can be a char, varchar2, nchar, nvarchar2, clob, or nclob.

NOTE

Below is a listing of unistring values that can be combined with other characters in the compose function.

Unistring Value	Resulting character
unistr('\0300')	grave accent ( ` )
unistr('\0301')	acute accent ( ´ )
unistr('\0302')	circumflex ( ^ )
unistr('\0303')	tilde ( ~ )
unistr('\0308')	umlaut ( ¨ )

APPLIES TO

The COMPOSE function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

Let's look at some Oracle COMPOSE function examples and explore how to use the COMPOSE function in Oracle/PLSQL.

For example:

COMPOSE('o' || unistr('\0308') )
Result: ö

COMPOSE('a' || unistr('\0302') )
Result: â

COMPOSE('e' || unistr('\0301') )
Result: é

ORACLE/PLSQL: DECOMPOSE FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL DECOMPOSE function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL DECOMPOSE function accepts a string and returns a Unicode string.

SYNTAX

The syntax for the Oracle/PLSQL DECOMPOSE function is:

DECOMPOSE( string )

Parameters or Arguments

string is the string that will be decomposed.

APPLIES TO

The DECOMPOSE function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

Let's look at some Oracle DECOMPOSE function examples and explore how to use the DECOMPOSE function in Oracle/PLSQL.

For example:

DECOMPOSE('Très bien')
Result: 'Tre`s bien'

DECOMPOSE('é')
Result: 'e´'

DECOMPOSE('olé')
Result: 'ole´'

ORACLE/PLSQL: CONVERT FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL CONVERT function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL CONVERT function converts a string from one character set to another.

SYNTAX

The syntax of the CONVERT function is:

CONVERT( string1 , char_set_to , [ char_set_from ] )

Parameters or Arguments

string1 is the string to be converted.

char_set_to is the character set to convert to.

char_set_from is the character set to convert from.

NOTE

Character sets include:

Character Set	Description
US7ASCII	US 7-bit ASCII character set
WE8DEC	West European 8-bit character set
WE8HP	HP West European Laserjet 8-bit character set
F7DEC	DEC French 7-bit character set
WE8EBCDIC500	IBM West European EBCDIC Code Page 500
WE8PC850	IBM PC Code Page 850
WE8ISO8859P1	ISO 8859-1 West European 8-bit character set

APPLIES TO

The CONVERT function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle CONVERT function examples and explore how to use the CONVERT function in Oracle/PLSQL.

For example:

CONVERT('A B C D E Ä Ê Í Õ Ø', 'US7ASCII', 'WE8ISO8859P1')
Result: 'A B C D E A E I ? ?'

ORACLE/PLSQL: TRANSLATE FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL TRANSLATE function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL TRANSLATE function replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time.

For example, it will replace the 1st character in the string_to_replace with the 1st character in the replacement_string. Then it will replace the 2nd character in the string_to_replace with the 2nd character in the replacement_string, and so on.

SYNTAX

The syntax for the Oracle/PLSQL TRANSLATE function is:

TRANSLATE( string1, string_to_replace, replacement_string )

Parameters or Arguments

string1 is the string to replace a sequence of characters with another set of characters.

string_to_replace is the string that will be searched for in string1.

replacement_string - All characters in the string_to_replace will be replaced with the corresponding character in thereplacement_string.

APPLIES TO

The TRANSLATE function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle TRANSLATE function examples and explore how to use the TRANSLATE function in Oracle/PLSQL.

For example:

TRANSLATE('1tech23', '123', '456')
Result: '4tech56'

TRANSLATE('222tech', '2ec', '3it')
Result: '333tith'

ORACLE/PLSQL: SOUNDEX FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL SOUNDEX function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL SOUNDEX function returns a phonetic representation (the way it sounds) of a string.

SYNTAX

The syntax for the Oracle/PLSQL SOUNDEX function is:

SOUNDEX( string1 )

Parameters or Arguments

string1 is the string whose phonetic value will be returned.

NOTE

The SOUNDEX function algorithm is as follows:

The SOUNDEX function return value will always begin with the first letter of string1.
The SOUNDEX function uses only the first 5 consonants to determine the NUMERIC portion of the return value, except if the first letter of string1 is a vowel.
The SOUNDEX function is not case-sensitive. What this means is that both uppercase and lowercase characters will generate the same SOUNDEX function return value.

APPLIES TO

The SOUNDEX function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle SOUNDEX function examples and explore how to use the SOUNDEX function in Oracle/PLSQL.

For example:

SOUNDEX('tech on the net')
Result: 'T253'

SOUNDEX('TECH ON THE NET')
Result: 'T253'

SOUNDEX('apples')
Result: 'A142'

SOUNDEX('apples are great')
Result: 'A142'

SOUNDEX('applus')
Result: 'A142'

ORACLE/PLSQL: DUMP FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL DUMP function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL DUMP function returns a varchar2 value that includes the datatype code, the length in bytes, and the internal representation of the expression.

SYNTAX

The syntax for the Oracle/PLSQL DUMP function is:

DUMP( expression, [return_format], [start_position], [length] )

Parameters or Arguments

expression is the expression to analyze.

return_format is optional. It determines the format of the return value. This parameter can be any of the following values:

Value	Explanation
8	octal notation
10	decimal notation
16	hexadecimal notation
17	single characters
1008	octal notation with the character set name
1010	decimal notation with the character set name
1016	hexadecimal notation with the character set name
1017	single characters with the character set name

start_position and length are optional parameters. They determines which portion of the internal representation to display. If these parameters are omitted, the DUMP function will display the entire internal representation in decimal notation.

APPLIES TO

The DUMP function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle DUMP function examples and explore how to use the DUMP function in Oracle/PLSQL.

For example:

DUMP('Tech')
Result: 'Typ=96 Len=4: 84,101,99,104'

DUMP('Tech', 10)
Result: 'Typ=96 Len=4: 84,101,99,104'

DUMP('Tech', 16)
Result: 'Typ=96 Len=4: 54,65,63,68'

DUMP('Tech', 1016)
Result: 'Typ=96 Len=4 CharacterSet=US7ASCII: 54,65,63,68'

DUMP('Tech', 1017)
Result: 'Typ=96 Len=4 CharacterSet=US7ASCII: T,e,c,h'

ORACLE/PLSQL: ABS FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL ABS function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL ABS function returns the absolute value of a number.

SYNTAX

The syntax for the Oracle/PLSQL ABS function is:

ABS( number )

Parameters or Arguments

number is the number to convert to an absolute value.

APPLIES TO

The ABS function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle ABS function examples and explore how to use the ABS function in Oracle/PLSQL.

For example:

ABS(-23)
Result: 23

ABS(-23.6)
Result: 23.6

ABS(-23.65)
Result: 23.65

ABS(23.65)
Result: 23.65

ABS(23.65 * -1)
Result: 23.65

ORACLE/PLSQL: CEIL FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL CEIL function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL CEIL function returns the smallest integer value that is greater than or equal to a number.

SYNTAX

The syntax for the Oracle/PLSQL CEIL function is:

CEIL( number )

Parameters or Arguments

number is the value used to find the smallest integer value.

APPLIES TO

The CEIL function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle CEIL function examples and explore how to use the CEIL function in Oracle/PLSQL.

For example:

CEIL(32.65)
Result: 33

CEIL(32.1)
Result: 33

CEIL(32)
Result: 32

CEIL(-32.65)
Result: -32

CEIL(-32)
Result: -32

ORACLE/PLSQL: FLOOR FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL FLOOR function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL FLOOR function returns the largest integer value that is equal to or less than a number.

SYNTAX

The syntax for the Oracle/PLSQL FLOOR function is:

FLOOR( number )

Parameters or Arguments

number is the value used to determine the largest integer value that is equal to or less than a number.

APPLIES TO

The FLOOR function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle FLOOR function examples and explore how to use the FLOOR function in Oracle/PLSQL.

For example:

FLOOR(5.9)
Result: 5

FLOOR(34.29)
Result: 34

FLOOR(-5.9)
Result: -6

ORACLE/PLSQL: ROUND FUNCTION (WITH NUMBERS)

This Oracle tutorial explains how to use the Oracle/PLSQL ROUND function (as it applies to numeric values) with syntax and examples.

DESCRIPTION

The Oracle/PLSQL ROUND function returns a number rounded to a certain number of decimal places.

SYNTAX (WITH NUMBERS)

The syntax for the Oracle/PLSQL ROUND function is:

ROUND( number, [ decimal_places ] )

Parameters or Arguments

number is the number to round.

decimal_places is the number of decimal places rounded to. This value must be an integer. If this parameter is omitted, the ROUND function will round the number to 0 decimal places.

APPLIES TO

The ROUND function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE - WITH NUMBERS

Let's look at some Oracle ROUND function examples and explore how to use the ROUND function in Oracle/PLSQL.

For example:

ROUND(125.315)
Result: 125

ROUND(125.315, 0)
Result: 125

ROUND(125.315, 1)
Result: 125.3

ROUND(125.315, 2)
Result: 125.32

ROUND(125.315, 3)
Result: 125.315

ROUND(-125.315, 2)
Result: -125.32

Discover Millions of Products your daily Need

Amazon::::Discover Millions of Products your daily Need

Amway Products in Hyderabad. All India Distributors (Certified Dietitian / Nutrition Advisor )

Amway Products in Kakinada. All India Distributors ( Certified Dietitian / Nutrition Advisor)

Monday, December 1, 2014

Oracle Tutorial 8

ORACLE/PLSQL: UPPER FUNCTION

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

APPLIES TO

EXAMPLE

FREQUENTLY ASKED QUESTIONS

ORACLE/PLSQL: INITCAP FUNCTION

DESCRIPTION

SYNTAX

Parameters or Arguments

APPLIES TO

EXAMPLE

ORACLE/PLSQL: LTRIM FUNCTION

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

APPLIES TO

EXAMPLE

ORACLE/PLSQL: RTRIM FUNCTION

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

APPLIES TO

EXAMPLE

ORACLE/PLSQL: TRIM FUNCTION

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

APPLIES TO

EXAMPLE

ORACLE/PLSQL: SUBSTR FUNCTION

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

APPLIES TO

EXAMPLE

ORACLE/PLSQL: REPLACE FUNCTION

DESCRIPTION

SYNTAX

Parameters or Arguments

APPLIES TO

EXAMPLE

ORACLE/PLSQL: INSTR FUNCTION

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

APPLIES TO

EXAMPLE

ORACLE/PLSQL: CASE STATEMENT

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

APPLIES TO

EXAMPLE

Comparing 2 Conditions

FREQUENTLY ASKED QUESTIONS

ORACLE/PLSQL: LPAD FUNCTION

DESCRIPTION

SYNTAX

Parameters or Arguments

APPLIES TO

EXAMPLE

ORACLE/PLSQL: RPAD FUNCTION

DESCRIPTION

SYNTAX

Parameters or Arguments