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

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s