PL/SQL Conversion

Conversion function – to_clob

The function to_clob() converts Long, varchar2 and char to clob. It converts the lob data from national character set to database character set.

Syntax:
to_clob(expression)

Example:

SELECT TO_CLOB('any data')
FROM dual

SELECT TO_CLOB(column_name)
FROM table_name

Conversion functions – case

The CASE function provides the use of IF-THEN-ELSE logic within a SQL statement. It is an

alternative for decode function.

Syntax:

CASE expr  WHEN comparison_expr1 THEN return_expr1
                [WHEN comparison_expr2 THEN return_expr2
		 ELSE else_expr ]
 END

Example:

SELECT  CASE ‘man’ WHEN  ‘lady’ THEN ‘hello lady’
	           WHEN  ‘man’ THEN ‘hello man’
	         ELSE  ‘hello someone’
	END
  FROM  DUAL

The output is hello man

SELECT  id, CASE (WHEN result ='P' THEN 'Pass'
                  WHEN result ='F' THEN 'Fail'
                  ELSE 'Withheld') 
            END
FROM   student

This will return for each student id:
If result = ‘P’ : ‘Pass’
If result = ‘F’ : ‘Fail’
Else : ‘Withheld’

Conversion Functions – convert

The Oracle/PLSQL function convert replaces a string from one character set to another. Convert does not translate words but substitutes the letter in one character set with the corresponding letter in another character set.

Syntax:

CONVERT( string, destination_char_set, [ source_char_set ] )

string is the string to be converted. destination_char_set is the character set into which the string has to be converted. source_char_set is optional and is the character set to convert the string from.

Following are the most commonly used character sets.
US7ASCII US 7-bit ASCII character set
F7DEC DEC French 7-bit character set
WE8ISO8859P1 ISO 8859-1 West European 8-bit character set

Example code:

SELECT CONVERT('A B C D Ä Ê Í Õ Ø', 'US7ASCII', 'WE8ISO8859P1') 
FROM dual

This returns ‘A B C D A E I ? ?’

Conversion functions – CAST()

The oracle function CAST converts one datatype to another. Cast does not support long or any LOB datatypes.

Syntax:

CAST(expression AS datatype)

Example code:

SELECT SYSDATE
FROM dual

12/28/2009
2:23:50 AM

SELECT CAST(SYSDATE AS VARCHAR(25))
FROM dual

28-DEC-09

Conversion Functions – DECODE()

DECODE is similar to if-then-else function. It takes a minimum of 4 arguments. The first argument is an expression or a value. The second argument is the value that has to be compared with the value in the first argument. The third argument is the value to be returned if there is a match between first and second argument. The fourth argument would be a default value returned if there is no match.

Syntax:

DECODE(expression, search_value, return_value, default_return_value)

Example code:

SELECT * FROM orders

order_id	amount	create_date	delivery_flag
101	500	10/13/2009	Y
102	200	11/20/2009	Y
103		11/11/2009	N

SELECT order_id, DECODE(delivery_flag, 'Y', 'Delivered', 'Not Delivered')
FROM orders

order_id	amount	create_date	delivery_flag
101	500	10/13/2009	Delivered
102	200	11/20/2009	Delivered
103		11/11/2009	Not Delivered

Conversion Functions – NVL()

The NVL function is used to replace NULL values by any other value. NVL takes two arguments. The first argument is the field to be checked for NULL. The second argument is the value that has to be replaced if the first argument returns NULL.

Syntax:

NVL( check_value, replace_value )

Example code:

SELECT * FROM orders

order_id	amount	create_date	last_updated_date
101	500	10/13/2009	10/25/2009
102	200	11/20/2009
103		11/11/2009	11/22/2009

SELECT order_id, NVL(amount, 600)
FROM orders

order_id	nvl(amount, 600)
101	500
102	200
103	600

This replaces every occurrence of null value in amount field with 600

SELECT order_id, NVL(last_updated_date, create_date)
FROM orders

order_id	nvl(last_updated_date, create_date)
101	10/25/2009
102	11/20/2009
103	11/22/2009

This replaces every occurrence of null value in last_updated_date with create_date