Create Oracle Function

Here is a SQL statement:

SELECT ROC_YM
FROM FMS451FA

The result is 

Owning to client's requirement, we need to add slash between Chinese year and month. Therefore, the SQL statement will adjust as bellows:

SELECT SUBSTR(ROC_YM, 1, 3)||'/'||SUBSTR(ROC_YM, 4, 2) AS ROC_YM
FROM FMS451FA

The result will be converted into:

   

Because it is a common requirement, and apply to all functions. So we can create an Oracle function to increase reusability.

Here is the syntax to create Oracle function

Hence, we create an Oracle function to add slash between Chinese year and month

CREATE OR REPLACE FUNCTION FORMAT_YYYMM(INPUT VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN SUBSTR(INPUT, 1, 3)||'/'||SUBSTR(INPUT, 4, 2); END;

Whenever we encounter this date formatting requirement, we can reuse this function as bellows:

SELECT FORMAT_YYYMM(ROC_YM)
FROM FMS451FA

Reference

[1] http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_5009.htm

[2] http://www.techonthenet.com/oracle/functions.php