How to Code Single Quotes with Q-Quote

Starting in 10g, use a 'q', a pair of single quotes, and a pair of brackets to code a literal that uses a single quote. With this method, you can code it exactly as it is.

Select q'!He's ok!'        As Single_Quote_In_Middle
     , q'['I like him.']'  As Single_Quote_At_Ends
     , q'(')'              As Single_Quote_Alone
     , q'<''>'             As Two_Single_Quotes
  From Dual;

To delete or insert a name with a single quote:

Delete From Emp Where EName = q'\O'MALLEY\';

Insert Into Emp ( EmpNo , EName         , DeptNo )
         Values ( 7474  , q'{O'MALLEY}' , 10     );
Commit;

To search for a name with a single quote in it:

Select EmpNo, Ename, DeptNo
 From Emp
Where Ename Like q'[%'%]';

Prior to 10g:

Select 'He''s ok'         As Single_Quote_In_Middle
     , '''I like him.'''  As Single_Quote_At_Ends
     , ''''               As Single_Quote_Alone
     , ''''''             As Two_Single_Quotes
  From Dual;

Oracle Exceptions found in DBA_Source

rem +--------------------------------------------------------------------------+
rem | Script ID:     Exceptions.sql
rem |
rem | Purpose:       Display a list of exception numbers assigned by Oracle.
rem |               
rem | Developer:     Lynn Tobias
rem | Script Date:   3/12/2009
rem | Oracle Ver:    10g
rem |
rem | Table(s) Used: DBA_Source
rem |               
rem | Output:        Exceptions.Txt
rem |
rem | Revisions:     Dvl Date     Ver Comment/Change
rem |                --- -------- --- -----------------------------------------+
rem +--------------------------------------------------------------------------+
Clear Screen
Column Exception Format A30     Heading 'Exception Name'
Column Name      Format A30     Heading 'DBA_Source Name'
Column Errnum    Format 9999999 Heading 'Exception'
Spool Exceptions.Txt

Select
       --- 1 ---------------------------------------------------------------------------------------
       -- 1.1 Replace:       There were so many variations that to make the regexp simpler, I 
       --                    removed all the spaces.
       -- 1.2 Replace(2):    I also removed the quotes from the few that used those on the numbers.
       -- 1.3 Regexp_Substr: Look for a string starting with a comma, then one or more of anything
       --                    and ending with a closed parenthesis.
       -- 1.4 Substr:        To remove the leading comma and the trailing parenthesis, start the 
       --                    substring at position two, and go for a length of the string minus 2.
       -- 1.5 To_Number:     Convert it to a number so it will sort correctly.
       ---------------------------------------------------------------------------------------------
               To_Number(
                         Substr(
                                Regexp_Substr(
                                              Replace(
                                                      Replace(Text,' ',Null)
                                                     ,'''',Null
                                                     )
                                              ,',.{1,}\)'
                                             ) 
                               ,2
                               ,Length(Regexp_Substr(Replace
                                        (Replace(Text,' ',Null)
                                        ,'''',Null),',.{1,}\)')
                                      )
                                      -2
                               )
                        ) 
               As
       Errnum
       --- 2 ---------------------------------------------------------------------------------------
       -- This is the name field from DBA_Source
       ---------------------------------------------------------------------------------------------
     ,         Initcap(Name)
               As
       Name      
       --- 3 ---------------------------------------------------------------------------------------
       -- 3.1 RegExp_Substr: Find the text starting with a open parenthesis for one or more letters 
       --                    and ending with a comma.
       -- 3.2 Substr:        Drop '(' and ',' from string by substringing starting at position 2, 
       --                    and going for a length of the original string minus 2.
       -- 3.3 InitCap:       Make it consistent and more readable
       ---------------------------------------------------------------------------------------------
     ,         InitCap(
                       Substr(
                              Regexp_Substr(
                                            Text
                                           ,'\(.{1,}\,'
                                           )
                             ,2
                             ,Length(Regexp_Substr(Text,'\(.{1,}\,'))
                              -2
                                    )
                      ) 
               As
       Exception
       ---------------------------------------------------------------------      
  From Dba_Source 
 Where Upper(Text) Like '%EXCEPTION_INIT%'
   And Text Not Like '--%'
 Order By Errnum Desc;

Prompt
Prompt Note: This list has been spooled to Exceptions.Txt

Line Breaks

To get a line break in your output data, use 'Chr(10).' It must be concatenated to the columns it's between

  1* Select 'Hi' || Chr(10) || 'There' From Dual
SQL> /

Hi
There

Regular Expressions

The create table statement and all the code to produce this report can be found here. Scroll down to the end to find the attachment: RegExp.Sql

This is the data the queries run against:

Select * From Fam;

NAME  PHONE           DSCR
----- --------------- --------------------------------------------------------------------------------
Me    555-123-0987    Fun-loving aunt with a lot of nieces and nephews. 111-123-4567
Don   555-234-2323    alternate phone (555)234-2222. Father Father of mostly-grown kids.
Craig 555-888-8888    at-home {nuts} {nuts} +45 8 676 49 86
Glenn 555-232-8888    (123) 242-2424
Dee   232-388-3333    -farmer 787.222.9834

Description to Match:  3 of anything, a dash, then 4 of anything

NAME  REGEXP_SUBSTR(DSCR,'...-....')
----- ------------------------------------------------------------------------------------------------
Me    Fun-lovi
Don   234-2222
Glenn 242-2424

Description to Match:  3 of anything, a dash, then 4 of anything

NAME  REGEXP_SUBSTR(DSCR,'.{3}-.{4}')
----- ------------------------------------------------------------------------------------------------
Me    Fun-lovi
Don   234-2222
Glenn 242-2424

BRACKET EXPRESSIONS

Description to Match: 3#s, -, 4

NAME  REGEXP_SUBSTR(DSCR,'[12345]{3}-.{4}')
----- ------------------------------------------------------------------------------------------------
Me    111-123-
Don   234-2222
Glenn 242-2424

Description to Match: 3 numbers(1-5), -, 4

NAME  REGEXP_SUBSTR(DSCR,'[1-5]{3}-.{4}')
----- ------------------------------------------------------------------------------------------------
Me    111-123-
Don   234-2222
Glenn 242-2424

Description to Match: 3 numbers,-,4

NAME  REGEXP_SUBSTR(DSCR,'[[:digit:]]{3}-.{4}')
----- ------------------------------------------------------------------------------------------------
Me    111-123-
Don   234-2222
Glenn 242-2424

Description to Match: Any 3 characters except for numbers,-,4

NAME  REGEXP_SUBSTR(DSCR,'[^[:digit:]]{3}-.{4}')
----- ------------------------------------------------------------------------------------------------
Me    Fun-lovi
Don   tly-grow

Description to Match: 3 numbers, A-G or X-z or 'n' or 'u' ,a dash then 4 more characters. 
Don's data doesn't show because he has an earlier digit value

NAME  REGEXP_SUBSTR(DSCR,'[[:digit:]A-GX-Znu]{3}-.{4}')
----- ------------------------------------------------------------------------------------------------
Me    Fun-lovi
Don   234-2222
Glenn 242-2424

Description to Match: 3 numbers, a literal period

NAME  REGEXP_SUBSTR(DSCR,'[[:digit:]]{3}\.')
----- ------------------------------------------------------------------------------------------------
Don   222.
Dee   787.

SUBEXPRESSIONS

(find a "+" then 1-4 sections of 1-3 digits followed by a space. 
The last grouping will be 1 or more numbers) 
i.e., ([0-9]{1,3} ) means 1-3 numbers followed by a space

Description to Match: International phone #

NAME  REGEXP_SUBSTR(DSCR,'\+([0-9]{1,3}){1,4}([0-9]+)')
----- ------------------------------------------------------------------------------------------------
Craig +45 8 676 49 86

ALTERNATION

(use '|')  i.e., (-|\.)

Description to Match: 3 numbers, - or ., then 4 of anything

NAME  REGEXP_SUBSTR(DSCR,'[[:digit:]]{3}(-|\.).{4}')
----- ------------------------------------------------------------------------------------------------
Me    111-123-
Don   234-2222
Glenn 242-2424
Dee   787.222.

Description to Match: 3 numbers, - or ., then 4 of anything

NAME  REGEXP_SUBSTR(DSCR,'[[:digit:]]{3}[.-].{4}')
----- ------------------------------------------------------------------------------------------------
Me    111-123-
Don   234-2222
Glenn 242-2424
Dee   787.222.

Description to Match: Phone numbers with dashes, periods or parens

NAME  REGEXP_SUBSTR(DSCR,'([0-9]{3}[.-]|\([0-9]{3}\)?)'||'[0-9]{3}[.-][0-9]{4}')
----- ------------------------------------------------------------------------------------------------
Me     111-123-4567
Don   (555)234-2222
Glenn (123) 242-2424
Dee    787.222.9834

GREEDINESS

Description to Match: Get a word ending in a space (gets all it can find)

.+[[:space:]]
-------------
In the

Description to Match: To get 1st word only

[^[:space:]]
------------
In

BACKREFERENCES

Description to Match: Find double-words

                                     REGEXP_SUBSTR(DSCR,'(^|[[:space:][:punct:]]+)([[:alpha:]]+)'||
NAME  DSCR                                                     '[[:space:][:punct:]]+\2')
----- ------------------------------ -----------------------------------------------------------------
Don   alternate phone (555)234-2222. Father . Father Father
      Father Father of mostly-grown 
      kids.
Craig at-home {nuts} {nuts} +45 8    {nuts} {nuts
      676 49 86

Sequence Basics

This shows how to create a basic sequence. CurrVal will show what number was last used. Whenever NextVal is referenced, the sequence gets incremented.

Set Feedback Off Heading Off
Drop Table TestTbl;
Create Table TestTbl
      ( Id   Number(4),
        Note Varchar2(20) );

Drop Sequence Test_Seq;
Create Sequence Test_Seq
         Increment By 1 Start With 1
         NoMaxValue NoCache NoCycle;

Insert Into TestTbl Values(Test_Seq.Nextval, 'Apple');
Select 'CurrVal: '||Test_Seq.CurrVal From Dual;

CurrVal: 1

Insert Into TestTbl Values(Test_Seq.Nextval, 'Nuts' );
Select 'CurrVal: '||Test_Seq.CurrVal From Dual;

CurrVal: 2

Select 'ID: '  ||ID         ||' '||
       'Note: '||Note
 from TestTbl;

ID: 1 Note: Apple
ID: 2 Note: Nuts

Select 'CurrVal: '||Test_Seq.CurrVal ||' '||
       'NextVal: '||Test_Seq.NextVal
  From Dual;

CurrVal: 3 NextVal: 3

/

CurrVal: 4 NextVal: 4

Trigger - Check for Positive Values

Create Or Replace Trigger PositiveOnly
 Before Insert Or Update
 Of DeptNo On Dept
 For Each Row
Begin
 If :new.DeptNo -20100, 'Please insert a positive value');
 End If;
End PositiveOnly;
/