ANSI Standards – Database – Substring

Background

Reviewing some SQL and wanted to point out a tiny dissimilarity between database platforms.

Data

Our test table is Oracle’s SCOTT.EMP

Here is what the data looks like :-

oracle.scott.tiger.PNG

Substring/Substr

The HIREDATE is the only well formatted fixed length string.

Its format is DD-MMM-YY.

Query

Oracle

SQL


select 

           HIREDATE

        , SUBSTR(HIREDATE, 8, 2) as "YEAR"

        , SUBSTR(HIREDATE, 1, 2) as "DAY"

        , SUBSTR(HIREDATE, 4, 2) as "MONTH"

from   SCOTT.EMP 

Output

oracle.scott.tiger.HireDate.PNG

Explanation

  1. Oracle relies on Substr to extract portions of a string

SQL Server

Database :- WideWorldImporters

Table :- Sales.Customers

Outline

Here are the first five rows of the Sales.Customers table in WideWorldImporters.

sales.customers

Our effort is to separate out the Street Number and Street Name.

SQL

SELECT TOP (100) 

        [CustomerID]

      , [CustomerName]

      , [DeliveryAddressLine1]

      , [DeliveryAddressLine2]

      , case
            when (charindex(' ', [DeliveryAddressLine2]) > 0)
                then
                    substring
                    (
                          [DeliveryAddressLine2]
                        , 1
                        , charindex(' ', [DeliveryAddressLine2])
                    )
            else null
        end as [streetNumber]			

      , case
            when (charindex(' ', [DeliveryAddressLine2]) > 0)
                then
                    substring
                    (
                          [DeliveryAddressLine2]
                        , charindex(' ', [DeliveryAddressLine2]) +1
                        , 100
                    )
            else null
        end as [streetName]			

FROM [WideWorldImporters].[Sales].[Customers]

Output

sales.customers.deliveryAddress2.PNG

Explanation

  1. SQL Server’s Transact SQL relies on Substring to extract out portions of a string

Database Platforms

Vendor Database Platform Substr/Substring
IBM LUW substr and substring
Microsoft SQL Server substring
MySQL MySQL substr
Oracle Oracle substr
PostgreSQL PostgreSQL substring

Summary

Microsoft SQL Server Transact SQL and PostgreSQL uses substring.

MySQL and Oracle uses substr.

IBM/DB2 support both substr and substring.

References

  1. IBM
    • Db2
  2.  PostgreSQL
    • Documentation
      • Functions and Operators
        Link

Oracle :- Numbers Table

Background

Let us create and populate a numbers table.

Code

Outline

Creating a numbers table is a bit more complicated.

And, it’s complexity is dependent on Version of Oracle.

Steps

  1. Create Table
    • Identity Column
      • To me identity column is a bit easier that Sequence
  2. Run Statement N Times
    • In Transact SQL, we use go
    • In Oracle
      • We can use the Hierarchy function “connect by level”
      • For Statement

SQL

SQL – Identity – Assign Default


create table "number"
    (
        "id" INTEGER
            GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1)
            NOT NULL

        , constraint "PK_Lab_Number"
            primary key
            (
                "id"
            )
    )

;
/

truncate table "number";
/

DECLARE
    l_time PLS_INTEGER;
    l_cpu PLS_INTEGER;

begin

    l_time := DBMS_UTILITY.get_time;
    l_cpu := DBMS_UTILITY.get_cpu_time;

    FOR iLoop IN 1..5000
    LOOP

        insert into "number"
        (
	        "id"
        )
        values
        (
	        default
        );

	END LOOP;

   DBMS_OUTPUT.put_line
      (
        'Time = ' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs '
      );

   DBMS_OUTPUT.put_line
        (
          'CPU Time = ' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs '
        );

end;

/

drop table "number";
/

SQL – Identity – Assign Explicit


create table "number"
    (
        "id" INTEGER
            -- GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1)
            GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1)
            NOT NULL

        , constraint "PK_Lab_Number"
            primary key
            (
                "id"
            )
    )

;
/

truncate table "number";
/

DECLARE
        l_time     PLS_INTEGER;
        l_cpu      PLS_INTEGER;
        lMaxNumber integer;

begin

    l_time := DBMS_UTILITY.get_time;
    l_cpu := DBMS_UTILITY.get_cpu_time;

    FOR iLoop IN 1..1
    LOOP

		insert into "number"
        (
	        "id"
        )
        with cte
		(
		    "vail"
		)
		as
		(
		    select rownum
            FROM   dual
            CONNECT BY LEVEL <= 5000
		)
        select
           cte."vail"

        from cte
		;

	END LOOP;

    DBMS_OUTPUT.put_line
      (
        'Time = ' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs '
      );

   DBMS_OUTPUT.put_line
        (
          'CPU Time = ' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs '
        );	

    select max("id")
    into   lMaxNumber
    from   "number"
    ;

   DBMS_OUTPUT.put_line
        (
          'lMaxNumber = ' || TO_CHAR(lMaxNumber)
        );	    

end;

/

drop table "number";
/

 

Oracle – Top N Records

Background

This last week, Mr. P and I were talking shop as to how difficult it can be to return “Top N Records” from a Query.

In SQL Server, it is not that difficult we simply use select top N.

For more difficult scenarios where grouping is needed, we use Windowing Functions.

 

Oracle

He emailed me what he came up with.  And, I promised to see how badly I will stumble.

Now that I discovered Oracle Live SQL, I know that I don’t even have to request access to our in-house Oracle database, I can just use a free Cloud DB Playground.

Oracle Live SQL

Table & Data

As always, I need a test table and some data.

Found out that Oracle Live SQL provides two schemas, HR for small form factors.

And, Sales History for bigger data.

Metadata

Let us see which tables are availed within the HR schema.


select 
          tbl.OWNER
        , tbl.TABLE_NAME
        , tbl.TABLESPACE_NAME
        , tbl.STATUS
        , tbl.SAMPLE_SIZE
        , tbl.LAST_ANALYZED
        
from   ALL_TABLES tbl

where tbl.OWNER in ('HR')

Output

 

Sample Queries

From Googling, found some good Q/A on StackOverflow.

Outline

They directed me at the following:

  1. Fetch First
  2. Analytic Function
  3. ROWNUM

 

Query – Get all Data

From reviewing ALL_TABLES, I know that I have only 10 records in the HR.JOB_HISTORY table.  And, so it is OK to simply return all records and order by specific ranking columns.

 


select 

         tblJH.END_DATE as dateEnd
       , tbLJH.START_DATE as dateStart
       , tblJH.EMPLOYEE_ID as employeeID
       , tblJH.JOB_ID as jobID
       , tblJH.DEPARTMENT_ID as departmentID

from   HR.JOB_HISTORY tblJH

order by
      tblJH.END_DATE desc

 

Output

 

Explanation

  1. Returns 10 records

 

Query – Fetch First

Fetch_First needs an ordered inner list.
And, within the First_First clause specify number of records.

select * 
 
from 
 
    ( 
          select  
              tblJH.END_DATE as dateEnded 
            , tblJH.START_DATE as dateStarted   
            , tblJH.EMPLOYEE_ID as employeeID 
            , tblJH.JOB_ID as jobID 
            , tblJH.DEPARTMENT_ID as departmentID 
            , ROWNUM as rowNumber 
       
        from   HR.JOB_HISTORY tblJH 
 
        order by 
                tblJH.END_DATE DESC 
              , tblJH.START_DATE DESC
              , tblJH.EMPLOYEE_ID ASC
     
    ) tblA 
     
FETCH FIRST 3 ROWS ONLY

Output

 

 

Query – Use Analytic Function

The Analytic Function pathway needs an inner select and within that an inner select a computed column.

That computed column is filled out by the system.

And, it can order or rank based on partition ( grouping) or against entire dataset.


select *  
   
from 
   
    (  
          select  
              tblJH.END_DATE as dateEnded  
            , tblJH.START_DATE as dateStarted    
            , tblJH.EMPLOYEE_ID as employeeID  
            , tblJH.JOB_ID as jobID  
            , tblJH.DEPARTMENT_ID as departmentID  
            , row_number()  
                 over  
                  ( 
                       order by  
                            tblJH.END_DATE desc 
                          , tblJH.START_DATE desc 
                          , tblJH.EMPLOYEE_ID asc 
                  ) as seqNbr 
         
        from   HR.JOB_HISTORY tblJH  
   
        order by 
            tblJH.END_DATE DESC 
       
    ) tblA  
       
where seqNbr <= 3 

Output

 

Query – Use RowNum

The RowNum query is the one that is the easiest to get a bit sloppy over.

And, it seems to need two sub-selects.

The innermost query will have the Order By.

The middle will add the ranking.

And, the outermost will filter based on the ranking.

-- Use RowNum
select *
from   (
            select  
                  dateEnded
                , dateStarted
                , employeeID
                , jobID
                , departmentID
                , ROWNUM as seqNbr
            from 
            -- get ordered data
            ( 
                select  
                      tblJH.END_DATE as dateEnded 
                    , tblJH.START_DATE as dateStarted   
                    , tblJH.EMPLOYEE_ID as employeeID 
                    , tblJH.JOB_ID as jobID 
                    , tblJH.DEPARTMENT_ID as departmentID 
                from   HR.JOB_HISTORY tblJH 
                order by 
                      tblJH.END_DATE DESC 
                    , tblJH.START_DATE DESC
                    , tblJH.EMPLOYEE_ID ASC
            ) tblA
        ) tblB    
-- filter based on seqNbr     
where seqNbr <= 3
;


References

  1. Oracle-Base
    • Row Limiting Clause for Top-N Queries in Oracle Database 12c Release 1 (12.1)
      Link
  2. Home / Database / Oracle Database Online Documentation, 10g Release 2 (10.2) / Administration
    • Database SQL Reference
  3. Vertabelo
    • Row_Number
      • Bartłomiej Jańczak
        • Oracle ROWNUM Explained
          Link
  4. Stack Overflow
    • How do I limit the number of rows returned by an Oracle query after ordering?
      Link
    • How to use Oracle ORDER BY and ROWNUM correctly?
      Link

Oracle Live SQL

Background

Needing to test out a Table creation script before posting it online.
SQL Server is easy, but not so much Oracle.

And, so googled for free online Oracle DB playground.

Oracle Live SQL

URL

Oracle Live SQL is available here.

Here is my welcome screen.

Account

Previous Account

Tried to use an old Oracle account that I have from yester years, but no go.

New Account

Signed up for a new Account.

Got sent an email and confirmed it is mine.

 

SQL

Sample SQL

Here is my sample SQL:


declare 
           numberofTableMatches number  := 0;
           objectName VARCHAR2(60);
           sqlStatement VARCHAR2(255);
           sqlMessage_ VARCHAR(255);
           
           table_name_ VARCHAR(60);

begin

  numberofTableMatches := 0;
  objectName := 'MetaStage_Loc_Info';

  SELECT count(*) 
  into   numberofTableMatches
  FROM   USER_TABLES
  where  (
                ( table_name = objectName )
            or  ( table_name = upper(objectName) )
        )    
  ;
  
  
  
  sqlMessage_ := 'numberofTableMatches ' ||  numberofTableMatches || '';
      
  dbms_output.put_line(sqlMessage_);  
  IF (numberofTableMatches = 10) THEN
  
      sqlStatement := '
create table MetaStage_Loc_Info
(
     Computer			VARCHAR2(64)
   , SoftwareProduct	VARCHAR2(64)
   , DataStore			VARCHAR2(64)
   , DataSchema		VARCHAR2(64)
)';

      
      dbms_output.put_line('Executing SQL pasted below :');
      
      dbms_output.put_line(sqlStatement);
      
      execute immediate sqlStatement;
      
      dbms_output.put_line('Executed SQL:');

  ELSE
  
      sqlMessage_ := 'Table (' || objectName || ') exists';
      
     dbms_output.put_line(sqlMessage_);
      
  END IF ;

end;

 

Image

 

Save Session

 

 

Saved Session