SQL Comparison - MS TSQL Oracle PL/SQL and MySQL SQL

Comparison of particular techniques, even if trivial in scope, can reveal the different philosophy between different systems, in this post the techniques for extracting the filename (aka basename) from a string in compared in Oracle SQL, T-SQL and MySQL.

 Extracting filename from path.

The task - extract all text after the last path seperator, for simplicity in these examples I assume the DOS reverse slash character '\', however any technique should handle both.

MS T-SQL.

This uses the reverse function to find the last seperator, and then reverses the substring to return it correctly:

declare @filepath varchar(256)
set @filepath = '\\sydnet01.fileserver.com.au\mmfile02\118688\03151060720004\441002041v11.doc'
select reverse(@filepath), reverse(substring(reverse(@filepath),0, charindex('\', reverse(@filepath))))

Oracle SQL.

This uses in the instr function which can be passed a negative start position to search for the last occurrence:

VARIABLE filepath VARCHAR2(256);
EXEC :filepath := '\\sydnet01.fileserver.com.au\mmfile02\118688\03151060720004\441002041v11.doc';
SELECT :filepath  FROM dual;
SELECT substr(:filepath,(instr(:filepath,'\',-1,1)+1),length(:filepath))FROM dual;

MySQL.

MySQL is even simpler than Oracle, the SUBSTRING_INDEX function returns the substring portion from a match. Like the Oracle instr function it supports a negative index.

SET @filepath = '\\\\sydnet01.fileserver.com.au\\mmfile02\\118688\\03151060720004\\441002041v11.doc';
SELECT SUBSTRING_INDEX(@filepath, '\\', -1) as filename;

Format Numbers With Commas.

When reporting large numbers being able to format them with commas or spaces for thousand seperators is useful.

MS T-SQL.

T-SQL does not have the ability to do this easily. One reason for this is because presentation shouldn't be included in SQL running against the database. While true (as far as it goes), it is useful to be able to run a SQL query and format the results, so it can be easily read and interpreted.
The method below converts (casts) the number to the money type, which then supports string conversion with adding commas as a thousands seperator (CONVERT(varchar, 2).

SELECT replace(CONVERT(varchar, CAST(987654321 AS money), 1),'.00','')

As the money format adds 2 decimal places, the replace function removes them.

Oracle SQL*Plus

The command line utility SQL*Plus has reporting functions built into it. To do simple output of large figures:

col count format 999,999,999
select count(*) as count, to_char(date_created, 'YYYY-MON') as monthcreated from docs where date_Created >'01-JAN-2012' group by to_char(date_created, 'YYYY-MON');
       COUNT MONTHCRE
------------ --------
      71,852 2012-APR
      63,757 2012-JAN
      64,045 2012-JUN
      81,481 2012-MAY
      14,494 2012-JUL
      79,554 2012-FEB
      93,214 2012-MAR

MySQL

Bulk Loading of Data.

SQL Server.

SQL Server has the bcp utility:

 bcp {dbtable | query} {in | out | queryout | format} datafile

Oracle

Oracle has sqlload utility, full details are at Orafaq wiki, an example:

load data
 infile *
 replace
 into table pf.doc_load
fields terminated by "," optionally enclosed by '"'
 (  doc_id
 )
begindata
119589984
119279372

or in a seperate file:

load data
 infile '/data/ora102/temp/mydata.csv'
 replace
 into table pf.doc_load
fields terminated by "," optionally enclosed by '"'
 (  doc_id
 )