Asktom. Oracle Regular Expressions. Oracle Native Dynamic SQL NDS. Oracle How to Disable all Constraints via Single SQL Command. Oracle Translate & Replace. Oracle Substring & Instring Functions Substr Instr. Examples in Oracle/PLSQL of using the substr() function to extract a substring from a string: The general syntax for the SUBSTR() function is: SUBSTR( source_string, start_position, [ length ] ) "source_string" is the original source_string that the substring will be taken from.
"start_position" is the position in the source_string where you want to start extracting characters. The first position in the string is always '1', NOT '0', as in many other languages. "length" is an optional parameter that specifies how many characters to extract. Notes: If the start_position is specified as "0", substr treats start_position as "1", that is, as the first position in the string. If the start_position is a positive number, then substr starts from the beginning of the string.
If the start_position is a negative number, then substr starts from the end of the string and counts backwards. If the length is a negative number, then substr will return a NULL value. Examples: substr('Dinner starts in one hour. Oracle DBMS_SQL Dynamic SQL. Morgan's Library Morgan Daniel Oracle SQL PL/SQL. Oracle PL/SQL Cheatsheet. This "cheat sheet" covers most of the basic functionality that an Oracle DBA needs to run basic queries and perform basic tasks.
It also contains information that a PL/SQL programmer will frequently use to write stored procedures. The resource is useful as a primer for individuals who are new to Oracle, or as a reference for those who are experienced at using Oracle. A great deal of information about Oracle exists, scattered throughout the net.
This resource was developed in order to make it easier for programmers and DBAs to find most of the basics in one place. When a topic is beyond the scope of a "cheatsheet" a link is generally provided for further research. Other Oracle References Oracle XML Reference - the XML reference is still in its infancy, but it will be coming along nicely before long. SELECT[edit] The SELECT statement is used to retrieve rows selected from one or more tables, object tables, views, object views, or materialized views.
SELECT INTO[edit] INSERT[edit] DELETE[edit] Oracle Sequences. Oracle Constraints Primary Key Foreign Unique Check Referential. Oracle DBMS_ERRLOG Error Logging. Oracle String Functions. To_char and to_nchar [Oracle SQL] TO_CHAR is an SQL function that converts a string, date, a datetime-interval or a number into a character string.
Converting a date If the first parameter is date type, it can be converted to a string representation. The appearance of that representation can be controlled with a Datetype format elements which are (optionally) in the second parameter. select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') from dual; Format strings Possible format strings when the first argument to to_char is a date: Converting numbers Suppressing leading or trailing blanks If a number is formatted with 0000, a blank will be added on the left side if the number is positive.
Converting a number into a hex string select to_char('419','xxx') from dual; Converting dates Abbriviated name of the day The abbrivation of a day's name (such as SUN, MON ... can be retrieved with the format specifier DY. Select to_char(sysdate,'DY','nls_date_language=english') from dual Spelling a number Weekday (day of week) nls parameters Thanks.
Expressions.