Useful SQL Snippets & Own Functions for SQL Server, SQL Server CE, MySQL, SQLite, PostgreSQL

Tables

Create table if not exists

Problem: Create table named "Table 1" with 3 columns ("id" - key, "First Name" - text, "Age" - integer), only if this table does not exist.

MySQL

CREATE TABLE IF NOT EXISTS `Table 1` (
  id INTEGER NOT NULL,
  `First Name` TEXT,
  Age INTEGER,
  PRIMARY KEY (id)
);

SQL Server

IF NOT EXISTS (SELECT * FROM SysObjects WHERE NAME = 'Table 1' AND XTYPE = 'U')
    CREATE TABLE [Table 1] (
        id int PRIMARY KEY NOT NULL,
        [First name] TEXT,
        Age INT
    )
GO

SQLite

CREATE TABLE IF NOT EXISTS [Table 1] (
   id INT PRIMARY KEY NOT NULL,
   'First Name' TEXT,
   Age INT
);

PostgreSQL

CREATE TABLE IF NOT EXISTS "Table 1" (
  id BIGSERIAL PRIMARY KEY,
  "First Name" TEXT,
  Age INT
);

Strings

Split

Problem: Get string part delimited by same characters or characters sequences. For example, get "Ipsum" from "Lorem Ipsum Dolor", or "Dolor" from "Lorem////Ipsum////Dolor".

There are no standard string split function in MySQL. Let's write our own! 

MySQL

DROP FUNCTION IF EXISTS STR_SPLIT;
DELIMITER $
CREATE FUNCTION STR_SPLIT (inp TEXT, del TEXT, ind INT) RETURNS TEXT DETERMINISTIC
BEGIN
  SET @right_part = SUBSTRING_INDEX(inp, del, ind);
  RETURN SUBSTRING_INDEX(@right_part, del, -1);
END$
DELIMITER ;

/* Test calls */

SELECT STR_SPLIT("Lorem Ipsum Dolor", " ", 1); /* returns Lorem */
SELECT STR_SPLIT("Lorem Ipsum Dolor", " ", 2); /* returns Ipsum */
SELECT STR_SPLIT("Lorem Ipsum Dolor", " ", 3); /* returns Dolor */

SELECT STR_SPLIT("Lorem////Ipsum////Dolor", "////", 1);
SELECT STR_SPLIT("Lorem////Ipsum////Dolor", "////", 2);
SELECT STR_SPLIT("Lorem////Ipsum////Dolor", "////", 3);

Split (alternative)

MySQL

DROP FUNCTION IF EXISTS STR_SPLIT;
DELIMITER $
CREATE FUNCTION STR_SPLIT (inp TEXT, del TEXT, ind INT) RETURNS TEXT DETERMINISTIC
BEGIN
  SET @s = inp;
  SET @result= "";

  SET @i = 1;

  REPEAT
    IF (LOCATE(del, @s) = 0) THEN
      RETURN @s;
    END IF;
    
    SET @del_ind = LOCATE(del, @s);

    SET @result = SUBSTRING(@s, 1, @del_ind - 1);

    SET @s = SUBSTRING(@s, @del_ind + LENGTH(del), LENGTH(@s) - @del_ind);

    SET @i = @i + 1;
  UNTIL @i > ind END REPEAT;

  RETURN @result;
END$
DELIMITER ;

/* Test calls */

SELECT STR_SPLIT("Lorem Ipsum Dolor", " ", 1);
SELECT STR_SPLIT("Lorem Ipsum Dolor", " ", 2);
SELECT STR_SPLIT("Lorem Ipsum Dolor", " ", 3);

SELECT STR_SPLIT("Lorem////Ipsum////Dolor", "////", 1);
SELECT STR_SPLIT("Lorem////Ipsum////Dolor", "////", 2);
SELECT STR_SPLIT("Lorem////Ipsum////Dolor", "////", 3);

Remove substring

Problem: Remove substring by its start and length. For example, get "Lom ipsum" from "Lorem ipsum".

MySQL

DROP FUNCTION IF EXISTS STR_REMOVE;
CREATE FUNCTION STR_REMOVE (inp TEXT, start INT, length INT) RETURNS TEXT DETERMINISTIC
  RETURN CONCAT(SUBSTRING(inp, 1, start - 1), SUBSTRING(inp, start + length, LENGTH(inp) - (start - 1 + length)));

SELECT STR_REMOVE("Lorem ipsum", 3, 2); /* Lom ipsum */

Date and Time

Get current date and time

MySQL

SELECT NOW();            /* 2015-03-06 15:24:30 -- Current Date & Time */
SELECT CURDATE();        /* 2015-03-06          -- Current Date */
SELECT CURTIME();        /* 15:24:30            -- Current Time */
SELECT UNIX_TIMESTAMP(); /* 1425644670          -- Current Date & Time in UNIX Timestamp Format */
SELECT UTC_TIMESTAMP();  /* 2015-03-06 12:24:30 -- Current UTC Date & Time */
SELECT UTC_DATE();       /* 2015-03-06          -- Current UTC Date */
SELECT UTC_TIME();       /* 12:24:30            -- Current UTC Time */

SQL Server

SELECT GETDATE();                   /* 2015-03-06 15:24:30.177 -- Current Date & Time */
SELECT CAST(GETDATE() AS DATE);     /* 2015-03-06              -- Current Date */
SELECT CAST(GETDATE() AS TIME);     /* 15:24:30.1770000        -- Current Time */
SELECT GETUTCDATE();                /* 2015-03-06 12:24:30.177 -- Current UTC Date & Time */
SELECT CAST (GETUTCDATE() AS DATE); /* 2015-03-06              -- Current UTC Date */
SELECT CAST (GETUTCDATE() AS TIME); /* 12:24:30.1770000        -- Current UTC Time */

SQLite

SELECT datetime(CURRENT_TIMESTAMP, 'localtime') /* 2015-03-06 15:24:30 -- Current Date & Time */
SELECT CURRENT_TIMESTAMP;                   /* 2015-03-06 12:24:30 -- Current UTC Date & Time */

PostgreSQL

SELECT current_timestamp; /* 2015-03-06 15:24:30.177+03 -- Current Date & Time */
SELECT current_date;      /* 2015-03-06                 -- Current Date */
SELECT current_time;      /* 15:24:30.177+03            -- Current Time */

Add or subtract some value from date or time

MySQL

SELECT DATE_ADD(CURDATE(), INTERVAL 1 DAY);      /* Tomorrow */
SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY);      /* Yesterday */
SELECT DATE_ADD(CURDATE(), INTERVAL 1 MONTH);    /* +1 month */
SELECT DATE_SUB(CURDATE(), INTERVAL 1 MONTH);    /* -1 month */
SELECT DATE_ADD(CURDATE(), INTERVAL 1 YEAR);     /* +1 year */
SELECT DATE_SUB(CURDATE(), INTERVAL 1 YEAR);     /* -1 year */
SELECT ADDTIME(CURTIME(), '01:00');              /* +1 hour */
SELECT SUBTIME(CURTIME(), '01:00');              /* -1 hour */
SELECT ADDTIME(CURTIME(), '00:01');              /* +1 minute */
SELECT SUBTIME(CURTIME(), '00:01');              /* -1 minute */
SELECT ADDTIME(CURTIME(), '00:00:01');           /* +1 second */
SELECT SUBTIME(CURTIME(), '00:00:01');           /* -1 second */

Calculate two dates or times differences

MySQL

SELECT DATEDIFF('2014-06-07', '2014-07-09'); /* -32 */
SELECT DATEDIFF('2014-07-09', '2014-06-07'); /* 32 */
SELECT TIMEDIFF('05:30:40', '02:00:00');     /* -03:30:40 */
SELECT TIMEDIFF('02:00:00', '05:30:40');     /* 03:30:40 */