There is not now, nor has there ever been, nor will there ever be, any programming language in which it is the least bit difficult to write bad code.

Replace Function

In Oracle/PLSQL, the replace function replaces a sequence of characters in a string with another set of characters.

The syntax for the replace function is:

replace( string1, string_to_replace, [ replacement_string ] )

string1 is the string to replace a sequence of characters with another set of characters.

string_to_replace is the string that will be searched for in string1.

replacement_string is optional. All occurrences of string_to_replace will be replaced with replacement_string in string1. If the replacement_string parameter is omitted, the replace function simply removes all occurrences of string_to_replace, and returns the resulting string.

 

Applies To:

  • Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

 

For example:

replace('123123tech', '123'); would return 'tech'
replace('123tech123', '123'); would return 'tech'
replace('222tech', '2', '3'); would return '333tech'
replace('0000123', '0'); would return '123'
replace('0000123', '0', ' '); would return '    123'

  Data Types
  Literals
  Declaring Variables
  IS NULL
  is-not-null
  IF-THEN-ELSE Statement
  Case Statement
  GOTO Statement
  Loop Statement
  FOR Loop
  CURSOR FOR Loop
  While Loop
  Repeat Until Loop
  Exit Statement
  Sequences
  Commit
  Rollback
  Set Transaction
  Lock Table
  Declare a Cursor
  OPEN Statement
  FETCH Statement
  CLOSE Statement
  Cursor Attributes
  SELECT FOR UPDATE Statement
  WHERE CURRENT OF Statement
  Procedure that outputs a dynamic PLSQL cursor
  Cursor within a cursor
  Cursor with variable in an "IN CLAUSE"

  • Built-In Functions

  •   Ascii Function
      AsciiStr Function
      Chr Function
      Compose Function
      Concat Function
      || Operator (concatenate)
      Convert Function
      Decompose Function
      Dump Function
      Initcap Function
      Instr Function
      Length Function
      Lower Function
      Lpad Function
      Ltrim Function
      Replace Function
      Rpad Function
      Rtrim Function
      Soundex Function
      Substr Function
      Translate Function
      Trim Function
      VSize Function
      Upper Function
      Bin_To_Num Function
      Cast Function
      CharToRowid Function
      From_Tz function
      HexToRaw Function
      NumToDSInterval Function
      NumToYMInterval Function
      RawToHex Function
      To_Char Function
      To_Clob Function
      To_Date Function
      To_DSInterval Function
      To_Lob Function
      To_Multi_Byte Function
      To_NClob Function
      To_Number Function
      To_Single_Byte Function
      To_Timestamp Function
      To_Timestamp_Tz Function
      To_YMInterval Function
      BFilename Function
      Cardinality Function
      Case Statement
      Coalesce Function
      Decode Function
      Group_ID Function
      Lag Function
      Lead Function
      LNNVL Function
      NANVL Function
      NULLIF Function
      NVL Function
      NVL2 Function
      Sys_Context Function
      Uid function
      User function
      UserEnv Function

  • Mathematical Functions

  •   Abs Function
      Acos Function
      Asin Function
      Atan Function
      Atan2 Function
      Avg Function
      Bin_To_Num Function
      BitAnd Function
      Ceil Function
      Corr Function
      Cos Function
      Cosh Function
      Covar_pop Function
      Covar_samp Function
      COUNT Function
      Cume_Dist Function
      Dense_Rank Function
      Exp Function
      Extract Function
      Floor Function
      Greatest Function
      Least Function
      Ln Function
      Log Function
      MAX Function
      Median Function
      MIN Function
      Mod Function
      Power Function
      Rank Function
      Remainder Function
      Round Function (with numbers)
      Round dates
      Sign Function
      Sin Function
      Sinh Function
      Sqrt Function
      StdDev Function
      SUM Function
      Tan Function
      Tanh Function
      Trunc (with numbers)
      Trunc (with dates)
      Var_pop Function
      Var_samp Function
      Variance Function

  • Date Functions

  •   Add_Months Function
      Current_Date function
      Current_Timestamp function
      DbTimeZone Function
      From_Tz function
      Last_Day Function
      LocalTimestamp function
      Months_Between Function
      New_Time Function
      Next_Day Function
      Round Function (with dates)
      SessionTimeZone Function
      Sysdate function
      SysTimestamp function
      To_Date Function
      Trunc Function (with dates)
      Tz_Offset Function
      SQLCODE Function
      SQLERRM Function
      User function2
      Uid function2
      Oracle System Tables
      Primary Keys

  • Foreign Keys

  •   Foreign-Keys
      Foreign Keys with cascade delete
      Foreign Keys with "set null on delete"
      Drop a foreign key
      Disable a foreign key
      Enable a foreign key

  • Unique Constraints

  •   Check Constraints

  • Indexes

  •   Creating Functions
      Creating Procedures

  • Creating Triggers

  •   BEFORE INSERT Trigger
      AFTER INSERT Trigger
      BEFORE UPDATE Trigger
      AFTER UPDATE Trigger
      BEFORE DELETE Trigger
      AFTER DELETE Trigger
      Drop a Trigger
      Disable a Trigger
      Disable all Triggers on a table
      Enable a Trigger
      Enable all Triggers on a table

  • Exception Handling

  •   Named System Exceptions
      Named Programmer-Defined Exceptions
      WHEN OTHERS Clause
      SQLCODE Function
      SQLERRM Function

  • Oracle Error Messages





  • affiliate_link