Oracle SQL Built-In Functions
Character/String Functions
| Function | Description | Example |
|---|---|---|
| substr(src, p, l) | Returns a portion of src starting at position p and l char. long | substr('Hello',3,2) = 'll' |
| upper | Converts to uppercase | upper('Hello') = 'HELLO' |
| lower | Converts to lowercase | lower('HELLO') = 'hello' |
| initcap | Capitalizes the first character or each word | initcap('HELLO, world!') = 'Hello, World!' |
| ltrim | Removes blanks at the left-end of a string | ltrim(' Hello ') = 'Hello ' |
| rtrim | Removes blanks at the right-end of a string | rtrim(' Hello' ) = ' Hello' |
| trim | Removes blanks at either ends of a string | trim(' Hello ') = 'Hello' |
| concat | Concatenates two strings | concat('Hel', 'lo') = 'Hello' |
| length | Returns the length of a string | length('Hello') = 5 |
| replace(src, x, y) | Replaces every occurrence of x in src by y (or by nothing if y is absent) |
replace('Hello', 'll', 'r') = 'Hero' replace('Hello', 'o') = 'Hell' |
| lpad(src, l, p) | Returns src, left-padded to length n with the string p | lpad('Hello', 10, '>') = '>>>>>Hello' |
| rpad(src, l, p) | Returns src, right-padded to length n with the string p | rpad('Hello', 10, '!') = 'Hello!!!!!' |
| chr | Returns a character's code | chr('H') = 72 |
| soundex | Computes the soundex phonetic value of a string | |
| translate | Replaces each char. from x in src by the corresponding char. in y | translate('hello', 'hl', 'HL') = 'HeLLo' |
| nls_initcap | Same as initcap with National Language Support (NLS) | |
| nls_upper | Same as upper with National Language Support (NLS) | nls_upper('Café) = CAFÉ |
| nls_lower | Same as lower with National Language Support (NLS) |
Date & Time Functions
| Function | Description | Example |
|---|---|---|
| sysdate | Returns the current date and time | |
| trunc(d, t) | Trancates a date d to the nearest time unit t (by default to the nearest day) | |
| round(d, t) | As before, but using rounding | select round(sysdate) from dual |
| add_months(d, n) | Returns d plus n months | add_months(sysdate, 6) |
| months_between(d1, d2) | Returns the number of months between dates d1 and d2 | |
| from_tz(d, tz) | Adds a timezone component tz to date d | |
| last_day(d) | Returns the last day of the month containing d | last_day(sysdate) |
Content
String Char. Functions
Date & Time Functions
See Also
Oracle SQL
Oracle PL/SQL
Share
Add to del.icio.us
String Char. Functions
Date & Time Functions
See Also
Oracle SQL
Oracle PL/SQL
Share
Add to del.icio.us