SQL Functions (for ODBC and Data Gateway)

SELECT REGEX_REPLACE('I like Cap0-red and Cap1-blue','Cap(\d+)-(\w+)','Hat$1-$2');

I like Hat0-red and Hat1-blue

SELECT REGEX_REPLACE('I like CAP0-red and cap1-blue','CaP(\d+)-(\w+)','Hat$1-$2',true);

I like Hat0-red and Hat1-blue

SELECT REGEX_ISMATCH('AA11BB','\d+');

1

SELECT REGEX_ISMATCH('11AABB','^\d+');

1

SELECT REGEX_ISMATCH('AABB11','\d+$');

1

SELECT REGEX_ISMATCH('11','^\d+$');

1

SELECT REGEX_ISMATCH('AA','^\d+$');

0

SELECT REGEX_ISMATCH('AA00','aa\d+');

0

SELECT REGEX_ISMATCH('AA00','aa\d+',true);

1

SELECT REGEX_SEARCH('>> 00AA 01BB 02CC 03DD <<<','(\d+)(\w+)');

00AA

SELECT REGEX_SEARCH('>> 00AA 01BB 02CC 03DD <<<','(\d+)(\w+)',-1);

03DD

SELECT REGEX_SEARCH('>> 00AA 01BB 02CC 03DD <<<','(\d+)(\w+)',0,2);

03

SELECT REGEX_SEARCH('>> 00AA 01BB 02CC 03DD <<<','(\d+)(\w+)',0,2);

DD

SELECT REGEX_SEARCH('>> 00AA 01BB 02CC 03DD <<<','(\d+)(\w+)',*);

00AA11BB22CC33DD

SELECT REGEX_SEARCH('>> 00AA 01BB 02CC 03DD <<<','(\d+)(\w+)',*,0,false,false,'-');

00AA-11BB-22CC-33DD

SELECT REGEX_SEARCH('>> 00AA 01BB 02CC 03DD <<<','(\d+)(\w+)',*,2,false,false,'-');

AA-BB-CC-DD

SELECT REGEX_SEARCH('>> 00AA 01BB 02CC 03DD <<<','(\d+)(\w+)',*,2);

AABBCCDD

SELECT BASE64_TO_BYTES('QWJjZA==');

Bytes[] for Abcd

SELECT CONCAT('AA','BB','CC');

AABBCC

SELECT CONCAT('AA',null,'BB');

(NULL)

SELECT CONCAT('AA','BB','CC','DD','EE','FF');

AABBCCDDEEFF

SELECT CONCAT('AA' || 'BB' || 'CC' || 'DD' || 'EE' || 'FF');

AABBCCDDEEFF

SELECT CONCAT('AA' || null || 'CC' );

(NULL)

SELECT LOWER('AbCd');

abcd

SELECT UPPER('AbCd');

ABCD

SELECT SUBSTR('ZappySys PowerPack', 1, 8);

ZappySys

SELECT SUBSTR('ZappySys PowerPack', 8, -8);

ZappySys

SELECT SUBSTR('ZappySys PowerPack', -1, 9);

PowerPack

SELECT TRIM('  *** xxABCxx ***  ');

*** xxABCxx ***

SELECT TRIM('  *** xxABCxx ***  ','x *');

ABC

SELECT TRIM('  *** xxABCxx ***  ','x *' || char(9) || char(13) || char(10));

ABC

SELECT TRIM(null);

(NULL)

SELECT LTRIM('  *** xxABCxx ***  ');

*** xxABCxx ***  

SELECT LTRIM('  *** xxABCxx ***  ','x *');

ABCxx ***  

SELECT LTRIM('  *** xxABCxx ***  ','x *' || char(9) || char(13) || char(10));

ABCxx ***  

SELECT LTRIM(null);

(NULL)

SELECT RTRIM('  *** xxABCxx ***  ');

  *** xxABCxx ***

SELECT RTRIM('  *** xxABCxx ***  ','x *');

 ***  xxABC

SELECT RTRIM('  *** xxABCxx ***  ','x *' || char(9) || char(13) || char(10));

xxABC

SELECT RTRIM(null);

(NULL)

SELECT LENGTH('ABC');

3

SELECT LENGTH('A B C');

5

SELECT LENGTH(null);

(NULL)

SELECT REPLACE('The sky is green','green','blue');

The sky is blue

SELECT REPLACE('The sky is green',null,'blue');

(NULL)

SELECT INSTR('The sky is green','The');

1

SELECT INSTR('The sky is green','is');

9

SELECT INSTR('The sky is green','Fake');

0

SELECT CHAR(65);

A

SELECT CHAR(66);

B

SELECT CHAR(2344);

न

SELECT CHAR(65,66,67);

ABC

SELECT CHAR(2344,2351,2344);

नयन

SELECT CHAR(13,10);

{newline}

SELECT CHAR(9);

{tab}

SELECT CHAR(null);

(NULL)

SELECT HEX('A');

41

SELECT HEX('*');

2A

SELECT HEX('ABC*');

4142432A

SELECT HEX(null);

(NULL)

SELECT RANDOM_STRING();

AbCd@!4124Xyz@%)*123

SELECT RANDOM_STRING(3);

Abc

SELECT FILE_WRITE_BINARY(RANDOM_IMAGE(),'c:\temp\dump.png')

byte[...]

SELECT BYTES_TO_BASE64(RANDOM_IMAGE())

iVBORw0KG....{many more}....rkJggg==

SELECT SOUNDEX('New york');

N620

SELECT SOUNDEX('newyork');

N620

SELECT SOUNDEX('Chicago');

C220

SELECT SOUNDEX('Chicagoo');

C220

SELECT SOUNDEX('Cheecago');

C220

SELECT SOUNDEX('Ceecago');

C220

SELECT SOUNDEX(null);

?000

SELECT SLEEP(2000);

1

SELECT JSON_VALUE('{location:{city:"Atlanta", country:"USA"}}','$.location.country');

USA

SELECT JSON_VALUE('{locationList : [{city:"Atlanta", country:"USA"},{city:"Mumbai", country:"INDIA"}]}','$.locationList[1].country');

INDIA

SELECT JSON_VALUE('{locationList : [{city:"Atlanta", country:"USA"},{city:"Mumbai", country:"INDIA"}]}','$.locationList[?(@city=='Mumbai')].country');

INDIA

SELECT JSON_ARRAY_FIRST('["Jan","Feb","Mar"]');

Jan

SELECT JSON_ARRAY_LAST('["Jan","Feb","Mar"]');

Mar

SELECT JSON_ARRAY_NTH('["Jan","Feb","Mar"]',0);

Jan

SELECT JSON_ARRAY_NTH('["Jan","Feb","Mar"]',2);

Mar

SELECT BYTES_TO_BASE64(BASE64_TO_BYTES('QWJjZA=='));

QWJjZA==

SELECT TEXT_TO_BASE64('Abcd');

QWJjZA==

SELECT BASE64_TO_TEXT('QWJjZA==');

Abcd

SELECT FILE_TO_BASE64('c:\temp\image.png');

iVBORw0KGgoAAAANSUhEUgAAABQAAAAUCAIAAAAC64paAAAACXBIWXMAAA7EAAAOxAGVKw4bAAAAB3RJTUUH5QMTASEFcmYpawAAAAd0RVh0QXV0aG9yAKmuzEgAAAAMdEVYdERlc2NyaXB0aW9uABMJISMAAAAKdEVYdENvcHlyaWdodACsD8w6AAAADnRFWHRDcmVhdGlvbiB0aW1lADX3DwkAAAAJdEVYdFNvZnR3YXJlAF1w/zoAAAALdEVYdERpc2NsYWltZXIAt8C0jwAAAAh0RVh0V2FybmluZwDAG+aHAAAAB3RFWHRTb3VyY2UA9f+D6wAAAAh0RVh0Q29tbWVudAD2zJa/AAAABnRFWHRUaXRsZQCo7tInAAAAZUlEQVQ4jWP84+TEQC5gIlsnpZpZ4CzmvXuJ1PPX2RldMwMDA/uGCQR1/gwogLMHg5+RAbLbIACrj7BrZmBg+LGWE87mCP6OVc3A+ZkGAcaA25+ENROTWhgGjZ8x0wZ+wDg0CwMApCQTh3PgswUAAAAASUVORK5CYII=

SELECT FILE_TO_BASE64('https://zappysys.com/downloads/files/test/small.png');

iVBORw0KGgoAAAANSUhEUgAAABQAAAAUCAIAAAAC64paAAAACXBIWXMAAA7EAAAOxAGVKw4bAAAAB3RJTUUH5QMTASEFcmYpawAAAAd0RVh0QXV0aG9yAKmuzEgAAAAMdEVYdERlc2NyaXB0aW9uABMJISMAAAAKdEVYdENvcHlyaWdodACsD8w6AAAADnRFWHRDcmVhdGlvbiB0aW1lADX3DwkAAAAJdEVYdFNvZnR3YXJlAF1w/zoAAAALdEVYdERpc2NsYWltZXIAt8C0jwAAAAh0RVh0V2FybmluZwDAG+aHAAAAB3RFWHRTb3VyY2UA9f+D6wAAAAh0RVh0Q29tbWVudAD2zJa/AAAABnRFWHRUaXRsZQCo7tInAAAAZUlEQVQ4jWP84+TEQC5gIlsnpZpZ4CzmvXuJ1PPX2RldMwMDA/uGCQR1/gwogLMHg5+RAbLbIACrj7BrZmBg+LGWE87mCP6OVc3A+ZkGAcaA25+ENROTWhgGjZ8x0wZ+wDg0CwMApCQTh3PgswUAAAAASUVORK5CYII=

SELECT BASE64_TO_FILE('c:\temp\image.png','iVBORw0KGgoAAAANSUhEUgAAABQAAAAUCAIAAAAC64paAAAACXBIWXMAAA7EAAAOxAGVKw4bAAAAB3RJTUUH5QMTASEFcmYpawAAAAd0RVh0QXV0aG9yAKmuzEgAAAAMdEVYdERlc2NyaXB0aW9uABMJISMAAAAKdEVYdENvcHlyaWdodACsD8w6AAAADnRFWHRDcmVhdGlvbiB0aW1lADX3DwkAAAAJdEVYdFNvZnR3YXJlAF1w/zoAAAALdEVYdERpc2NsYWltZXIAt8C0jwAAAAh0RVh0V2FybmluZwDAG+aHAAAAB3RFWHRTb3VyY2UA9f+D6wAAAAh0RVh0Q29tbWVudAD2zJa/AAAABnRFWHRUaXRsZQCo7tInAAAAZUlEQVQ4jWP84+TEQC5gIlsnpZpZ4CzmvXuJ1PPX2RldMwMDA/uGCQR1/gwogLMHg5+RAbLbIACrj7BrZmBg+LGWE87mCP6OVc3A+ZkGAcaA25+ENROTWhgGjZ8x0wZ+wDg0CwMApCQTh3PgswUAAAAASUVORK5CYII=');

SELECT FILE_READ_TEXT('c:\temp\test.txt');

abcd

SELECT FILE_READ_TEXT('c:\temp\some-bad-path.txt');

--ERROR--

SELECT FILE_READ_TEXT('c:\temp\some-bad-path.txt',1);

(NULL)

SELECT FILE_READ_BINARY('c:\temp\test.txt');

abcd

SELECT FILE_READ_BINARY('c:\temp\some-bad-path.txt');

--ERROR--

SELECT FILE_READ_BINARY('c:\temp\some-bad-path.txt',1);

(NULL)

SELECT FILE_WRITE_TEXT('AbCd','c:\temp\test.txt');

4

SELECT FILE_WRITE_BINARY(BASE64_TO_BYTES('QWJjZA=='));

4

SELECT HASH_TEXT('md5','abc123');

e99a18c428cb38d5f260853678922e03

SELECT HASH_TEXT('md5','abc123','hex',0);

e99a18c428cb38d5f260853678922e03

SELECT HASH_TEXT('md5','abc123','hex',1);

E99A18C428CB38D5F260853678922E03

SELECT HASH_TEXT('md5','abc123','base64');

6ZoYxCjLONXyYIU2eJIuAw==

SELECT HASH_TEXT('sha256','abc123');

6ca13d52ca70c883e0f0bb101e425a89e8624de51db2d2392593af6a84118090

SELECT HASH_TEXT('sha256','abc123','base64');

bKE9UspwyIPg8LsQHkJaiehiTeUdstI5JZOvaoQRgJA=

SELECT HASH_FILE('md5','c:\myfile.txt');

e99a18c428cb38d5f260853678922e03

SELECT HASH_FILE('md5','c:\myfile.txt','hex',0);

e99a18c428cb38d5f260853678922e03

SELECT HASH_FILE('md5','c:\myfile.txt','hex',1);

E99A18C428CB38D5F260853678922E03

SELECT HASH_FILE('md5','c:\myfile.txt','base64');

6ZoYxCjLONXyYIU2eJIuAw==

SELECT HASH_FILE('sha256','c:\myfile.txt');

6ca13d52ca70c883e0f0bb101e425a89e8624de51db2d2392593af6a84118090

SELECT HASH_FILE('sha256','c:\myfile.txt','base64');

bKE9UspwyIPg8LsQHkJaiehiTeUdstI5JZOvaoQRgJA=

SELECT ABS(-4);

4

SELECT ABS(4);

4

SELECT ABS(null);

(NULL)

SELECT SIGN(-4);

-1

SELECT SIGN(4);

1

SELECT SIGN(+4);

1

SELECT SIGN(0);

0

SELECT SIGN(null);

(NULL)

SELECT ROUND();

-123234

SELECT ROUND(0);

123234

SELECT ROUND(0,10);

5

SELECT ROUND(-10,10);

5

SELECT RANDOM_INT();

-123234

SELECT RANDOM_INT(0);

123234

SELECT RANDOM_INT(0,10);

5

SELECT RANDOM_INT(-10,10);

5

SELECT RANDOM_LONG();

-123234

SELECT RANDOM_LONG(0);

123234

SELECT RANDOM_LONG(0,10);

5

SELECT RANDOM_LONG(-10,10);

5

SELECT RANDOM_INT();

-123234

SELECT RANDOM_INT(0);

123234

SELECT RANDOM_INT(0,10);

5

SELECT RANDOM_INT(-10,10);

5

SELECT AVG(Amount) FROM Orders

5000

SELECT AVG(DISTINCT Amount) FROM Orders

4000

SELECT SUM(Amount) FROM Orders

5000

SELECT SUM(Amount) FROM Orders

4000

SELECT COUNT(*) FROM Orders

5000

SELECT COUNT(DISTINCT *) FROM Orders

4000

SELECT COUNT(DISTINCT OrderId) FROM Orders

3000

SELECT MIN(Amount) FROM Orders

3000

SELECT MAX(Amount) FROM Orders

5000

SELECT GROUP_CONCAT(Country) FROM Customers

US,India,UK,US

SELECT GROUP_CONCAT(DISTINCT Country) FROM Customers

US,India,UK

SELECT GROUP_CONCAT(Country,';') FROM Customers

US;India;UK

SELECT 
 DATE('now') --e.g. 2021-04-28
,DATE('now','localtime') --e.g. 2021-04-28
,DATE('now','utc') --e.g. 2021-04-28
,DATE('now', 'start of month') --e.g. 2021-04-01	
,DATE('now', 'start of month', '+1 month' , '-1 day' ) --e.g. 2021-04-30	
,DATE('now', '+5 day') --e.g. 2021-05-03	
,DATE('now', '+5 day','+1 month') --e.g. 2021-06-03
,DATE('now', '+5 day','+1 month','+10 year') --e.g. 2031-06-03
,DATE('2020-01-01 22:00', '+2 hour') --e.g. 2020-01-02	

--see SQL comment for output--

SELECT 
 DATETIME('now') --e.g. 2021-04-28 04:05:55
,DATETIME('now','+2 hour') --e.g. 2021-04-28 06:05:55
,DATETIME('now','+2 hour','+5 minute') --e.g. 2021-04-28 06:10:55
,DATETIME('now','localtime') --e.g. 2021-04-28 04:05:55
,DATETIME('now','utc') --e.g. 2021-04-28 02:05:55
,DATETIME(1092941466, 'unixepoch') --e.g. 2004-08-19 18:51:06

--see SQL comment for output--

SELECT 
 TIME('now') --e.g. 04:15:19
,TIME('now','+2 hour') --e.g. 06:15:19
,TIME('now','+2 hour','+5 minute') --e.g. 06:20:19
,TIME('now','+2 hour','+5 minute','+10 second') --e.g. 06:20:29

--see SQL comment for output--

SELECT 
,JULIANDAY('now') --e.g. 2459332.67731214	
,JULIANDAY('2016-10-18') --e.g. 2457679.5	
,JULIANDAY('2016-10-18 16:45') --e.g. 2457680.19791667	
,JULIANDAY('2016-10-18 16:45:30') --e.g. 2457680.19826389

--see SQL comment for output--

SELECT 
 STRFTIME('%Y-%m-%dT%H:%M:%f','now') --e.g. 2021-04-28T04:15:19.000	
,STRFTIME('%Y-%m-%dT%H:%M:%f','now','+2 hour') --e.g. 2021-04-28T06:15:19.000
,STRFTIME('%s', '2004-08-19 18:51:06') --e.g. unixepoch 1092941466	and to get date from epoch try DATETIME(1092941466, 'unixepoch')

--see SQL comment for output--

SELECT NULLIF('A','B');

A

SELECT NULLIF('A','A');

(NULL)

SELECT NULLIF('A',null);

A

SELECT IFNULL('ABCD','N/A');

ABCD

SELECT IFNULL('ABCD',null);

ABCD

SELECT IFNULL(null,'N/A');

N/A

SELECT IFNULL(null,null);

(NULL)

SELECT IFNULL(UPPER(null),'N/A');

N/A

SELECT COALESCE('A','B','C');

A

SELECT COALESCE(1+1,null,3+3);

2

SELECT COALESCE(null,'B','C');

B