Oracle PLSQL Tutorial Conversion Functions 81 codes

  1. Home
  2. Oracle PLSQL Tutorial
  3. Conversion Functions
1 Cast date to char
2 Cast decimal to integer
3 CAST examples
4 Cast null as timestamp
5 Cast number to a char
6 Cast string to date type before comparison
7 Cast table of numbers
8 Cast varchar to char
9 Cast(12 98 as number(2)) example1
10 COALESCE(state, NA)
11 Conversion Functions
12 Convert single-byte G to its multibyte representation
13 Convert that multibyte representation back to a single byte
14 Convert the string 97 13 to a number using TO_NUMBER() and then adds 25 5 to that number
15 Converts the string -$12,345 67 to a number, passing the format string $99,999 99 to TO_NUMBER()
16 Decode as If statement
17 Decode column data to output more meaningful information
18 DECODE in the GROUP BY clause
19 Demo range comparison with DECODE
20 Format Parameters
21 HEXTORAW
22 NVL Function is used if there are null values
23 NVL() converts a null to a known value
24 Nvl2(comm,12msal+comm,12msal) as yearsal
25 NVL2(x, value1, value2) returns value1 if x is not null
26 Pass column values to TRANSLATE()
27 Pass multiple search and result parameters to DECODE()
28 ROWIDTOCHAR(x)
29 ROWIDTONCHAR(x)
30 Select cast(null as date ) example3
31 SELECT CAST(NULL AS TIMESTAMP WITH LOCAL TIME ZONE) from dual
32 Select cast(oak as char(10) ) example2
33 SELECT DECODE(1, 2, 1, 3)
34 Select translate(beer bucket,beer,milk) as translate
35 Table with clob and blob column
36 The null may be made more explicit with a DECODE statement
37 TO_CHAR(0 67, B9 99)
38 TO_CHAR(0012345 6700, FM99999 99)
39 TO_CHAR(12345 67, $99,999 99)
40 TO_CHAR(12345 67, 099,999 99) (4)
41 TO_CHAR(12345 67, 99,999 99) (1)
42 TO_CHAR(-12345 67, 99,999 99) (3)
43 TO_CHAR(12345 67, 99,999 9900)
44 TO_CHAR(-12345 67, 99,999 99MI)
45 TO_CHAR(-12345 67, 99,999 99PR)
46 TO_CHAR(12345 67, 99999 99) (2)
47 TO_CHAR(12345 67, 99999 99EEEE)
48 TO_CHAR(12345 67, 99999D99)
49 TO_CHAR(12345 67, 99999G99)
50 TO_CHAR(12345 67, 99999V99)
51 TO_CHAR(12345 67, C99,999 99)
52 TO_CHAR(12345 67, L99,999 99)
53 TO_CHAR(12345 67, TM)
54 TO_CHAR(12345 67, U99,999 99)
55 TO_CHAR(2007, RN)
56 To_char(bdate,fmMonth ddth, yyyy)
57 TO_CHAR(last_ddl_time,dd-mon-yyyy hh24
58 To_char(sysdate, Day, nls_date_language=Dutch)
59 To_char(sysdate,DAY dy Dy)
60 To_char(sysdate,hh24
61 To_char(sysdate,MONTH mon) as month
62 To_char(to_date(01012006,ddmmyyyy),is on Day) as new_year_2006
63 TO_CHAR(x [, format]) converts x to a string
64 To_number(123)
65 TO_NUMBER(x [, format]) converts x to a number
66 Translate replaces a string character by character
67 Use coalesce during the table join
68 Use Date to cast string to date type
69 Use decode as if statement and output high or low
70 Use decode create dynamic select statement
71 Use Decode in table column
72 Use NVL() to convert date columns
73 Use nvl() to convert null value to 0
74 Use nvl2 to deal with null salary
75 Use the NVL() against a non-initialized BOOLEAN variable
76 Use to_char more than once to create long date format
77 Use TO_CHAR() to convert columns containing numbers to strings
78 Use TRANSLATE() to convert numbers
79 Use TRANSLATE() with column
80 Using the DECODE() Function
81 Using the TRANSLATE() Function