HOME BZU Mail Box Online Games Radio and TV Live Cricket Score All Albums
Go Back   BZU PAGES: Find Presentations, Reports, Student's Assignments and Daily Discussion; Bahauddin Zakariya University Multan Institute of Computing Bachelor of Science in Information Technology BsIT 4th Semester Introduction to Database

Introduction to Database By Sir Yousaf


Reply
 
Thread Tools Search this Thread Rating: Thread Rating: 1 votes, 2.00 average. Display Modes
  #1  
Old 12-03-2009, 04:32 AM
.BZU.'s Avatar


 

Join Date: Sep 2007
Location: near Govt College of Science Multan Pakistan
Posts: 9,675
Contact Number: Removed
Program / Discipline: BSIT
Class Roll Number: 07-15
.BZU. has a reputation beyond repute.BZU. has a reputation beyond repute.BZU. has a reputation beyond repute.BZU. has a reputation beyond repute.BZU. has a reputation beyond repute.BZU. has a reputation beyond repute.BZU. has a reputation beyond repute.BZU. has a reputation beyond repute.BZU. has a reputation beyond repute.BZU. has a reputation beyond repute.BZU. has a reputation beyond repute
Read SQL Plus Functions in Oracle (single row functions)


SQL Plus Functions in Oracle

There are two types of functions.
  • Multiple Row Functions:
  • Single row functions: these function operate on single row and reteun one result per row.e.g. characters, numbers, date and conversion functions.
Name:  single_row_function.gif
Views: 3871
Size:  4.9 KB

NVL function
The NVL function takes two arguments,

NVL(x, y)
where both x and y are expressions. This function returns y, if x is NULL. If x is not NULL, it returns x.

select comm; nvl (comm,0) from emp;
NVL Function converts all null values into different values , in above query it convert null values into 0,

Here is the list of some useful functions

LENGTH(s) Returns the number of characters in the string s. LENGTH('Welcome Sir') = 11 LPAD(s, i[, s1])Returns the string s expanded in length to i characters, using s1 to fill in space as needed on the left side. LPAD('test', 11, '*-') = '*-*-*-*test' INITCAP(s) Returns the string s with the first character of each word in uppercase and all others in lowercase. INITCAP('hello SIR') = 'Hello Sir' CONCAT(s1, s2) Returns string s2 appended to s1. CONCAT('Wel', 'come') = 'Welcome' LOWER(s) Returns the string s with all characters in lowercase. LOWER('Welcome Sir') = 'welcome sir' UPPER(s) Returns the string s with all characters in upper case. UPPER('Welcome Sir') = 'WELCOME SIR' SUBSTR(s, pos[, len]) Returns the portion of the string s that is len characters long, beginning at position pos. If pos is negative, the position is counted backwards from the end of the string. The function returns NULL if len is less or equal to zero. If len is skipped, it returns the remaining of s. SUBSTR('welcome', 4) = 'come' INSTR(s, sub[, pos[, n]]) Returns the numeric character position in s where the n-th occurrence of sub is found. The search begins at the position pos in s. If the substring not found, it returns 0. If the pos is negative, the search is performed backwards (from right to left). The default values for pos and n are 1. INSTR('test', 't', 2) = 4

Some Numeric Functions

ROUND(d, i) Returns d rounded to i digits of precision to the right of the decimal point. If i is negative, d is rounded to the left of the decimal point. TRUNC(d, i) Returns d truncated to i digits of precision to the right of the decimal point. If i is negative, d is truncated to the left of the decimal point. MOD(i1, i2) Returns i1 modulo i2, or the reminder of i1 divided i2.
Date and time Functions (Conversion)

TO_CHAR(x[, fmt[, nls]]) Takes up to three arguments, where x is either a date or a number, fmt is a format string specifying the format that x will appear in, and nls specifies language or location formatting string. If x is a date, fmt is a date format code. If x is a number, fmt is a numeric format code. TO_DATE(s[, fmt[, nls]]) Converts string s to DATE datatype accordingly the format string fmt. The fmt string uses the same date format code. The default is the value stored in the NLS_DATE_FORMAT session variable.
Decode Function is same like if else statements

DECODE(x, v1, r1[, v2, r2 ...][, d]) The function compares the value of the expression x with values of the arguments v1, v2, etc. If the value of x is equivalent to v1, then r1 is returned; otherwise the additionalmatching expressions are compared. If no match is found and the default expression d is included, then the d is returned. If no match is found and there is no d argument, then NULL is returned. Example: SELECT sid, serial#, username, DECODE(command, 0, 'None', 2, 'Instert', 3, 'Select', 6, 'Update', 7, 'Delete', 8, 'Drop', 'Other') cmd FROM v$session WHERE type <> 'BACKGROUND';

Click here to See all other functions or download the attachment below.

Attached Files
File Type: html Single-row functions. IN SQL Plus Oracle Queries.html (31.6 KB, 746 views)
__________________
(`v)
`*..*`

.*.*) .*)
(.* (.
Bzu Forum

Don't cry because it's over, smile because it happened
Reply With Quote
Reply

Tags
functions, oracle, row, single, sql


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Templated Functions bonfire Introduction to Computing 0 26-02-2011 11:17 PM
Lesson 13: More on Functions bonfire Introduction to Computing 0 26-02-2011 10:41 PM
Lesson 7: Functions bonfire Introduction to Computing 0 26-02-2011 10:28 PM
Lesson 4: Functions bonfire Introduction to Computing 0 26-02-2011 10:24 PM
1st Topic: Functions of the Oprtaing System .BZU. Operating Systems 1 31-01-2008 03:35 AM

Best view in Firefox
Almuslimeen.info | Cloud Computing | Dedicated server hosting
Note: All trademarks and copyrights held by respective owners. We will take action against any copyright violation if it is proved to us.

All times are GMT +5. The time now is 12:06 AM.

Powered by vBulletin® Version 3.8.2
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.