{"id":14772,"date":"2020-04-27T21:47:19","date_gmt":"2020-04-27T21:47:19","guid":{"rendered":"https:\/\/ittutorial.org\/?p=14772"},"modified":"2020-04-27T21:47:20","modified_gmt":"2020-04-27T21:47:20","slug":"pl-sql-conversion-decoding-null-related-functions","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/pl-sql-conversion-decoding-null-related-functions\/","title":{"rendered":"PL\/SQL Conversion\/Decoding\/NULL-Related Functions"},"content":{"rendered":"

Hi Folks,<\/p>\n

 <\/p>\n

Conversion<\/strong> functions convert a value from one datatype to another. Generally, the form of the function names follows the convention datatype TO datatype. The NULL-related<\/strong> functions facilitate null handling. The encoding and decoding<\/strong> functions let you inspect and decode data in the database.<\/p>\n

The following list contains 7 of the most used conversion, decoding, and null-related functions.<\/p>\n

 <\/p>\n

DECODE<\/strong><\/h2>\n

Syntax: DECODE(expr, search, result, default)<\/p>\n

Description: DECODE compares expr to each search value one by one. If expr is equal to a search, then Oracle Database returns the corresponding result. If no match is found, then Oracle returns default. If default is omitted, then Oracle returns null.<\/p>\n

The decode function is one of the most used functions in PL\/SQL and plays a vital place in any system. It works like a group of if\/else and you can add as many expressions as you need.<\/p>\n

declare\r\n   v_var varchar2(20) := '';\r\n   \r\nbegin \r\n  for i in 1..3\r\n  loop\r\n     select DECODE(i, 1, 'Line 1',\r\n                      2, 'Line 2',\r\n                      'Default Line')\r\n            into v_var\r\n     from dual;\r\n     \r\n     dbms_output.put_line(v_var);\r\n  end loop;\r\nend;<\/pre>\n
\"\"<\/p>\n
 <\/p>\n
COALESCE<\/strong><\/h2>\n
Syntax: COALESCE(expr1, expr2, … exprn)<\/p>\n
Description: COALESCE returns the first non-null expr in the expression list. At least one expr must not be the literal NULL. If all occurrences of expr evaluate to null, then the function returns null.<\/p>\n
select name, name_ascii,  COALESCE(name_ascii, name) \"name_ascii_2\"\r\nfrom city \r\nwhere name_ascii is null;<\/pre>\n
\"\"<\/p>\n
 <\/p>\n
NVL<\/strong><\/h2>\n
Syntax: NVL(expr1, expr2)<\/p>\n
Description: If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.<\/p>\n
select name, name_ascii,  nvl(name_ascii, name) \"NVL\"\r\nfrom city \r\nwhere name_ascii is null;<\/pre>\n
\"\"<\/p>\n
 <\/p>\n
TO_CHAR<\/strong><\/h2>\n
Syntax: TO_CHAR(param, fmt)<\/p>\n
Description: TO_CHAR (param) converts any param as NCHAR, NVARCHAR2, CLOB, NCLOB, datetime, interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, NUMBER, BINARY_FLOAT, or BINARY_DOUBLE to a value of VARCHAR2 datatype, using the optional number format fmt.<\/p>\n
select TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') \"TO_CHAR_DATETIME\", \r\nTO_CHAR(1234) \"TO_CHAR_NUMBER\"\r\nfrom dual;<\/pre>\n
\"\"<\/p>\n
 <\/p>\n
TO_DATE<\/strong><\/h2>\n
Syntax: TO_DATE(char, fmt)<\/p>\n
Description: TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype. The fmt is a datetime model format specifying the format of char. If you omit fmt, then char must be in the default date format. If fmt is J, for Julian, then char must be an integer.<\/p>\n
SELECT TO_DATE(\r\n    'April 27, 2020, 11:00 A.M.',\r\n    'Month dd, YYYY, HH:MI A.M.',\r\n     'NLS_DATE_LANGUAGE = American') \"TO_DATE\"\r\n     FROM DUAL;<\/pre>\n
\"\"<\/p>\n
 <\/p>\n
TO_TIMESTAMP<\/strong><\/h2>\n
Syntax: TO_TIMESTAMP(char, fmt)<\/p>\n
Description: TO_TIMESTAMP converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype.<\/p>\n
SELECT TO_TIMESTAMP ('27-Apr-20 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') \"TO_TIMESTAMP\"\r\n   FROM DUAL;<\/pre>\n
\"\"<\/p>\n
 <\/p>\n
TO_NUMBER<\/strong><\/h2>\n
Syntax: TO_NUMBER(char)<\/p>\n
Description: TO_NUMBER converts expr to a value of NUMBER datatype. The expr can be a BINARY_FLOAT or BINARY_DOUBLE value or a value of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype containing a number in the format specified by the optional format model fmt.<\/p>\n
SELECT TO_NUMBER('10') + TO_NUMBER('5') \"TO_NUMBER\"\r\n   FROM DUAL;<\/pre>\n
\"\"<\/p>\n
 <\/p>\n
Please write down in the comments any further questions you may have. I\u2019ll be happy to help you.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Hi Folks,   Conversion functions convert a value from one datatype to another. Generally, the form of the function names follows the convention datatype TO datatype. The NULL-related functions facilitate null handling. The encoding and decoding functions let you inspect and decode data in the database. The following list contains 7 of the most used …<\/p>\n","protected":false},"author":10477,"featured_media":14709,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1994,3647],"tags":[9416,9417,9418,3617,3717,9413,9414,9415],"class_list":["post-14772","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-oracle","category-pl-sql","tag-oracle-conversion-functions","tag-oracle-decoding-functions","tag-oracle-null-related-functions","tag-oracle-pl-sql","tag-oracle-plsql","tag-pl-sql-conversion-functions","tag-pl-sql-decoding-functions","tag-pl-sql-null-related-functions"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/04\/PLSQL_ICO.png","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/14772","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/10477"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=14772"}],"version-history":[{"count":6,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/14772\/revisions"}],"predecessor-version":[{"id":14785,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/14772\/revisions\/14785"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/14709"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=14772"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=14772"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=14772"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}