Search This Blog

Thursday, December 30, 2010

Select a random records SQL SERVER, Oracle, MySql

Database > Random Records

--Select random rows with Microsoft SQL Server
--NEWID Creates a unique value of type uniqueidentifier.

SELECT * FROM table ORDER BY NEWID()

--Select random rows with MySQL
SELECT * FROM table ORDER BY RAND()

--Select random records with Oracle:
SELECT * FROM table ORDER BY dbms_random.value









How to extract words out from a string c#

C# > String > Spilt
 
Split returns a string array that contains the substrings in this instance that are delimited by elements of a specified Unicode character array. 


Example
 
string _strInput = "aaa bbb ccc ddd";
string[] words = _strInput.Split ( new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries );
foreach(string s in words)
{
  // value of s: aaa, bbb, ccc, ddd
}






Wednesday, September 1, 2010

Null data in Oracle using the ORDER BY clause

From:
Control null data in Oracle using the ORDER BY clause
Date: June 19th, 2007
Author: Bob Watkins

NULL data, which is sometimes called “absent” data, can be difficult to work with in relational databases. When a query contains the ORDER BY clause to sort the output, NULL data will sort to the bottom if the sort is in ascending order (ASC) and to the top if the sort is in descending order (DESC). In effect, NULL is treated as a very large value by Oracle.

This can create reports that are difficult to read. Consider a simple query in Oracle’s HR sample schema. Let’s say you want to list the names and commission percents of all employees in descending order, including those who get no commission (commission_pct is NULL). The following simple query does this:

SELECT employee_id, last_name, first_name, commission_pct
FROM employees
ORDER BY commission_pct DESC;

The problem is that all employees with no commission come out on top. You have to read through them all to find those who actually have a commission.

Starting with Oracle 8i, there is a little known syntax in the ORDER BY clause that fixes this. All you have to do is change the last line above to the following:

ORDER BY commission_pct DESC NULLS LAST;

The null rows will sort to the bottom after all the rows that contain commission data. You can also use NULLS FIRST when you’re sorting in ascending order, and you want the NULL rows to appear at the top of the report.

If you’re still supporting Oracle 8.0 or 7.3 databases, you can achieve the same effect using the Null Values function (NVL). Use something like the following in your ORDER BY:

ORDER BY NVL(commission_pct, -1);

This forces the NULL rows to be sorted as if they had the value (-1) in them, and they will appear at the bottom of the output. You won’t see the (-1) values because the query only sorts by the NVL function — it doesn’t display it in the SELECT lis

Monday, June 7, 2010

Oracle/PLSQL: Instr Function

Oracle/PLSQL > Functions > Instr

Instr function returns the location of a substring in a string.

The syntax for the instr Oracle function is:

instr( string1, string2 [, start_position [, nth_appearance ] ] )

Example

select INSTR('test', 'e') from dual

result:
2






Monday, May 17, 2010

Oracle/PLSQL: Oracle System Tables

Oracle/PLSQL > Oracle System Tables

--------------------------------------------------------------------------------

Below is an alphabetical listing of the Oracle system tables that are commonly used.

System Table Description

ALL_ARGUMENTS Arguments in object accessible to the user
ALL_CATALOG All tables, views, synonyms, sequences accessible to the user
ALL_COL_COMMENTS Comments on columns of accessible tables and views
ALL_CONSTRAINTS Constraint definitions on accessible tables
ALL_CONS_COLUMNS Information about accessible columns in constraint definitions
ALL_DB_LINKS Database links accessible to the user
ALL_ERRORS Current errors on stored objects that user is allowed to create
ALL_INDEXES Descriptions of indexes on tables accessible to the user
ALL_IND_COLUMNS COLUMNs comprising INDEXes on accessible TABLES
ALL_LOBS Description of LOBs contained in tables accessible to the user
ALL_OBJECTS Objects accessible to the user
ALL_OBJECT_TABLES Description of all object tables accessible to the user
ALL_SEQUENCES Description of SEQUENCEs accessible to the user
ALL_SNAPSHOTS Snapshots the user can access
ALL_SOURCE Current source on stored objects that user is allowed to create
ALL_SYNONYMS All synonyms accessible to the user
ALL_TABLES Description of relational tables accessible to the user
ALL_TAB_COLUMNS Columns of user's tables, views and clusters
ALL_TAB_COL_STATISTICS Columns of user's tables, views and clusters
ALL_TAB_COMMENTS Comments on tables and views accessible to the user
ALL_TRIGGERS Triggers accessible to the current user
ALL_TRIGGER_COLS Column usage in user's triggers or in triggers on user's tables
ALL_TYPES Description of types accessible to the user
ALL_UPDATABLE_COLUMNS Description of all updatable columns
ALL_USERS Information about all users of the database
ALL_VIEWS Description of views accessible to the user
DATABASE_COMPATIBLE_LEVEL Database compatible parameter set via init.ora
DBA_DB_LINKS All database links in the database
DBA_ERRORS Current errors on all stored objects in the database
DBA_OBJECTS All objects in the database
DBA_ROLES All Roles which exist in the database
DBA_ROLE_PRIVS Roles granted to users and roles
DBA_SOURCE Source of all stored objects in the database
DBA_TABLESPACES Description of all tablespaces
DBA_TAB_PRIVS All grants on objects in the database
DBA_TRIGGERS All triggers in the database
DBA_TS_QUOTAS Tablespace quotas for all users
DBA_USERS Information about all users of the database
DBA_VIEWS Description of all views in the database
DICTIONARY Description of data dictionary tables and views
DICT_COLUMNS Description of columns in data dictionary tables and views
GLOBAL_NAME global database name
NLS_DATABASE_PARAMETERS Permanent NLS parameters of the database
NLS_INSTANCE_PARAMETERS NLS parameters of the instance
NLS_SESSION_PARAMETERS NLS parameters of the user session
PRODUCT_COMPONENT_VERSION version and status information for component products
ROLE_TAB_PRIVS Table privileges granted to roles
SESSION_PRIVS Privileges which the user currently has set
SESSION_ROLES Roles which the user currently has enabled.
SYSTEM_PRIVILEGE_MAP Description table for privilege type codes. Maps privilege type numbers to type names
TABLE_PRIVILEGES Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
TABLE_PRIVILEGE_MAP Description table for privilege (auditing option) type codes. Maps privilege (auditing option) type numbers to type names





Saturday, May 15, 2010

Oracle FOR LOOP

The syntax for the FOR Loop in Oracle is:

FOR loop_counter IN [REVERSE] lowest_number..highest_number
LOOP
{.statements.}
END LOOP;





Get current day , month, year Oracle

Oracle > Date Time > Get current day , month, year Oracle


select to_number(to_char(sysdate,'dd')) day,to_number(to_char(sysdate,'mm')) month,to_number(to_char(sysdate,'yyyy')) year from dual;