SQL Data-Type conversion functions
SQL conversion functions are single row functions designed to alter the nature of the data type of a column value, expression, or literal. TO_CHAR, TO_NUMBER, and TO_DATE are the three most widely used conversion functions and are discussed in detail. The TO_CHAR function converts numeric and date information into characters, while TO_NUMBER and TO_DATE convert character data into numbers and dates, respectively
We know that when we store data inside a column, a number column cannot store character information. A date column cannot store random characters or numbers. However, a character equivalent of both number and date can be stored inside a VARCHAR2 column.
IMPLICIT DATA TYPE CONVERSION
Most of the time, if a function accepts a character input finds a number instead, Oracle automatically converts it into its character equivalent. If a function that accepts a number or a date parameter encounters a character value, there are specific conditions under which automatic data type conversion occurs. DATE and NUMBER data types are very strict compared to VARCHAR2 and CHAR.
Values that do not share identical data types with function parameters are implicitly converted to the required format if possible. VARCHAR2 and CHAR data types are collectively referred to as character types. Character fields are flexible and allow the storage of almost any type of information. Therefore, DATE and NUMBER values can easily be converted to their character equivalents. These conversions are known as a number to character and date to character conversions. Consider the following queries
Query 1
SELECT length(1234567890) FROM dual;Query 2
SELECT length(SYSDATE) FROM dual;Both queries use the LENGTH function, which takes a character string parameter. The
number 1234567890 in query 1 is implicitly converted into a character string, 1234567890, before being evaluated by the LENGTH function, which returns the number 10.
Query 2 first evaluates the SYSDATE function, which is assumed to be 07-APR-38. This date is implicitly converted into the character string 07-APR-38 and the LENGTH function returns the number 9.
Query 3
SELECT mod(’11’,2) FROM dual;Query 4
SELECT mod(‘11.123’,2) FROM dual;Query 5
SELECT mod(‘11.123.456’,2) FROM dual;Query 6
SELECT mod(‘$11’,2) FROM dual;Queries 3 and 4 implicitly convert the character strings 11 and 11.123 into the numbers 11 and 11.123, respectively, before the MOD function evaluates them and returns the results 1 and 1.123.
Query 5 returns the error ORA-1722: invalid number when Oracle tries to perform an implicit character to number conversion. It fails because the string 11.123.456 is not a valid number.
Query 6 also fails with the invalid number error, since the dollar symbol cannot be implicitly converted into a number.
EXPLICIT DATA TYPE CONVERSION
Oracle offers many functions to convert items from one data type to another, known as explicit data type conversion functions. These return a value guaranteed to be the type required and offer a safe and reliable method of converting data items. We have three main data type conversion functions
TO_CHAR: NUMBER and DATE values can be converted explicitly into CHARACTER values
TO_NUMBER: CHARACTER values can be converted explicitly into NUMBER values
TO_DATE: CHARACTER values can be converted explicitly into DATE values
TO_CHAR Function
There are two main types of conversions you must know when it comes to character conversion
Converting numbers to characters
Converting dates to characters
CONVERTING NUMBERS TO CHARACTERS
The TO_CHAR function returns an item of data type VARCHAR2. When applied to items of type NUMBER, several formatting options are available. The syntax is as follows
TO_CHAR(number1, [format])
The number1 parameter is mandatory and must be a value that either is or can be implicitly converted into a number. The optional format parameter may be used to specify numeric formatting information such as width, currency symbol, the position of a decimal point, and group (or thousands) separators, and must be enclosed in single quotation marks.
Example
Query 1
SELECT to_char(00001)||’ is a special number’ FROM dual;Query 2
SELECT to_char(00001,’0999999′)||’ is a special number’ FROM dual;Query 1 evaluates the number 00001, removes the leading zeros, converts the number 1 into the character 1, and returns the character string 1 is a special number.
Query 2 applies the numeric format mask ‘0999999’ to the number 00001, converting it into the character string 0000001. After concatenation to the character literals, the string returned is 0000001 is a special number.
The zero and six 9s in the format mask indicate to the TO_CHAR function that leading zeros must be displayed and that the display width must be set to seven characters. Therefore, the string returned by the TO_CHAR function contains seven characters.
NUMERIC Format Masks
You can use the below formats with TO_CHAR function syntax in order to convert numbers into specific characters
TO_CHAR(12,’9999′) will convert the number 12 as it is into character 12. Use the format ‘9’ to output the number as it is into character format
TO_CHAR(12,’09999′) will add leading 0s and output 00012. Use 0 in the format to output number into character format with leading 0s
TO_CHAR(030.40,’09999.999′) specifies the position of the decimal point. The output will be 00030.400
TO_CHAR(030.40,’09999D999′) D is a decimal separator (period is by default). Output will be 00030.400
TO_CHAR(03040,’09999,999′) specify the position of coma symbol. Output will be 00003,040
TO_CHAR(03040,’09999G999′) G is group separator (coma is by default). Output will be 00003,040
TO_CHAR(03040,’$099999′) output will be $003040
TO_CHAR(-3040,’99999MI’) specifies the position of minus sign. Output will be 3040-
TO_CHAR(3040,’S’) + or – sign is prefixed. Output will be +3040
CONVERTING DATES TO CHARACTERS
You can take advantage of a variety of format models to convert DATE items into almost any character representation of a date using TO_CHAR. Its syntax is as follows
TO_CHAR(date1, [format])
Only the date1 parameter is mandatory and must take the form of a value that can be implicitly converted to a date. The optional format parameter is case sensitive and must be enclosed in single quotes.
Consider the following three queries
Query 1
SELECT to_char(sysdate)||’ is today”s date’ FROM dual;Query 2
SELECT to_char(sysdate,’Month’)||’is a special time’ FROM dual;Query 3
SELECT to_char(sysdate,’fmMonth’)||’is a special time’ FROM dual;If the current system date is 03/JAN/09 and the default display format is DD/MON/RR, then Query 1 returns the character string 03/JAN/09 is today’s date.
There are two notable components in Query 2
The month component of the current system date is extracted for conversion to a character type.
Since the format mask is case sensitive and Month appears in the title case, the string returned is January is a special time. There is no need to add a space in front of the literal is a special time.
The TO_CHAR function automatically pads the name of the month with zero or more spaces to yield a nine-character-long string. Since January is eight characters long, one space is added, but if the month was September, no spaces would be automatically added.
If the format mask in query 2 was MONTH, the string returned would be JANUARY is a special time. The fm modifier is applied to Query 3, and the resultant string is January is a special time.
Note: There is no space between January and the literal is a special time as a result of the fm modifier.
DATE Format Masks
Below are the date format masks that you can use with Days, Months and Years to convert dates into a specific format
Use format element Y to get last digit of the year. Ex, 5
Use format element YY to get last two digits of year. Ex, 75
Use format element YYY to get last three digits of year. Ex, 975
Use format element YYYY to get four digits of year. Ex, 1975
Use RR to get two digit year. Ex, 75
Use YEAR to get case sensitive English spelling of year. Ex, NINETEEN SEVENTY-FIVE
Use MM to get two-digit month. Ex, 06
Use MON to get three-letter abbreviation of month. Ex, JUN
Use MONTH to get case sensitive English spelling of month. Ex, JUNE
Use D to get day of the week. Ex, 2
Use DD to get two-digit day of the month. Ex, 13
Use DDD to get day of the year, Ex, 153
Use DY to get three-letter abbreviation of day. Ex, MON
Use DAY to get case sensitive English spelling of day. Ex, MONDAY
DATE Format Masks for Time
You can use below formats with TO_CHAR function syntax in order to convert time component of a date into specific characters
Use AM, PM, A.M, P.M to specify the meridian indicators
Use HH, HH12 and HH24 to specify hour of the day, 1-12 hours and 0-23 hours
Use MI to output minutes of a time (0-59)
Use SS to output seconds of a time (0-59)
use SSSSS to output seconds past midnight (0-86399)
Miscellaneous DATE Formats
Below are the miscellaneous date formats available that you can use with TO_CHAR function
Use (~ / . , ? # !) formats to use as separators. Ex, ‘MM.YY’ will output 07.18
Use “any character literal”. Ex, ‘ “Week” W “of” Month’ will output Week 2 of September
Use TH for positional or ordinal text. Ex, ‘DDth “of” Month’ will output 12TH of September
Example
Execute below query and find out the output
SELECT 'Employee '||employee_id||' quit as '||job_id||' on '||
to_char(end_date,'fmDay "the "ddth "of" Month YYYY') "Quitting Date"
From hr.employees
Order by end_date;Example: You are required to retrieve a list of FIRST_NAME and LAST_NAME values and an expression based on the HIRE_DATE column for employees hired on a Saturday. The expression must be aliased as START_DATE and a HIRE_DATE value of 17-FEB-1996 must return the following string Saturday, the 13th of January, Two Thousand One
SELECT FIRST_NAME, LAST_NAME, TO_CHAR(HIRE_DATE, 'fmDay, "the
"ddth "of" Month, Yyyysp.') START_DATE
FROM HR.EMPLOYEES
WHERE TO_CHAR(HIRE_DATE,'fmDay') = 'Saturday'TO_DATE Function
The TO_DATE function returns an item of type DATE. Character strings converted to dates may contain all or just a subset of the date-time elements comprising a DATE. When strings with only a subset of the date time elements are converted, Oracle provides default values to construct a complete date. Components of character strings are associated with different date time elements using a format model or mask. The syntax is as follows
TO_DATE(string1, [format])
Only the string1 parameter is mandatory and if no format mask is supplied, string1 must take the form of a value that can be implicitly converted into a date. The optional format parameter is almost always used and is specified in single quotation marks. The format masks are identical to those listed in the TO_CHAR function above.
Consider the following five queries
Query 1
SELECT to_date(’25-DEC-2010′) FROM dual;Query 2
SELECT to_date(’25-DEC’) FROM dual;Query 3
SELECT to_date(’25-DEC’, ‘DD-MON’) FROM dual;Query 4
SELECT to_date(’25-DEC-2010 18:03:45′, ‘DD-MON-YYYY HH24:MI:SS’) FROM dual;Query 5
SELECT to_date(’25-DEC-10′, ‘fxDD-MON-YYYY’) FROM dual;Query 1 evaluates the string 25-DEC-2010 and has sufficient information to
implicitly convert it into a DATE item with a default mask of DD-MON-YYYY. The hyphen separator could be substituted with another punctuation character. Since no time components are provided, the time for this converted date is set to midnight or 00:00:00.
Query 2 cannot implicitly convert the string into a date because there is insufficient information and an “ORA-01840: input value is not long enough for date format” error is returned. By supplying a format mask DD-MON to the string 25-DEC.
Query 3 Oracle can match the number 25 to DD and the abbreviated month name DEC to the MON component. Year and time components are absent, so the current year returned by the SYSDATE function is used and the time is set to midnight. If the current year is 2009, Query 3 returns the date 25/DEC/09 00:00:00.
Query 4 performs a complete conversion of a string with all the date-time elements present, and no default values are supplied by Oracle.
Query 5 uses the fx modifier in its format mask. Since the year component of the string is 10 and the corresponding format mask is YYYY, the fx modifier results in an “ORA-01862: the numeric value does not match the length of the format item” error being returned.
Example:
SELECT first_name, last_name, hire_date from hr.employees
where hire_date > to_date('01/12/1988','mm/dd/yyyy')
order by hire_date;TO_NUMBER Function
The TO_NUMBER function returns an item of type NUMBER. Character strings converted into numbers must be suitably formatted so that any nonnumeric components are translated or stripped away with an appropriate format mask. The syntax is as follows:
TO_NUMBER(string1, [format])
Only the string1 parameter is mandatory and if no format mask is supplied, it must be a value that can be implicitly converted into a number. The optional format parameter is specified in single quotation marks.
Consider the following two queries
Query 1
SELECT to_number(‘$1,000.55’) FROM dual;Query 2
SELECT to_number(‘$1,000.55′,’$999,999.99’) FROM dual;Query 1 cannot perform an implicit conversion to a number because of the dollar sign, comma, and period and it returns the error ORA-1722: invalid number.
Query 2 matches the dollar symbol, comma, and period from the string to the format mask, and, although the numeric width is larger than the string width, the number 1000.55 is returned.
Related Posts
Heading 2
Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.