sql-match

Match a string using an SQL pattern.

This library is basically a spec-compliant implementation of a LIKE between two strings:

SELECT 'string' LIKE '%ing';  --> true 

Supported features:

• % wildcard sequence
• _ wildcard
• \ escape

Unsupported features:

• Custom escape character (ESCAPE)
• Ignored trailing spaces (MySQL's =)
• Disallowed trailing escape character (PostgreSQL)
• Collated international characters (COLLATE with =)
• [charlist] patterns (Access and SQL Server)
• ? and # wildcards (Access)

Installation

Node.js >= 8 is required. To install, type this at the command line:

npm install sql-match

Usage

isSQLMatch(pattern, testString)

const {isSQLMatch} = require('sql-match');
 
isSQLMatch('string', 'string');  //-> true
 
isSQLMatch('%ing', 'string');  //-> true
isSQLMatch('s%ng', 'string');  //-> true
isSQLMatch('str%', 'string');  //-> true
 
isSQLMatch('_tring', 'string');  //-> true
isSQLMatch('st__ng', 'string');  //-> true
isSQLMatch('strin_', 'string');  //-> true

Optionally, you can create a reusable/cacheable regular expression to improve performance:

const {sqlToRegex} = require('sql-match');
 
const pattern = sqlToRegex('%ing');
 
['string','stringing'].every(testString => pattern.test(testString));
//-> true

Gotchas

Because JavaScript strings are interpreted, you may want to use String.raw to avoid some annoyances that reduce consistency with SQL.

Non-wildcard escape sequences are possible:

isSQLMatch('\t', '	');  //-> true
isSQLMatch('\u0020', ' ');  //-> true
// or
isSQLMatch(String.raw`\t`, 't');  //-> true
isSQLMatch(String.raw`\u0020`, 'u0020');  //-> true

SELECT 't' LIKE '\t';  --> true 
SELECT 'u0020' LIKE '\u0020';  --> true 

Matching a literal wildcard will require you to escape the escape character:

isSQLMatch('\\%trin\\_', '%trin_');  //-> true
// or
isSQLMatch(String.raw`\%trin\_`, '%trin_');  //-> true

SELECT '%trin_' LIKE '\%trin\_';  --> true 

Matching a literal backslash will require you to escape the escaped escape character:

isSQLMatch('\\\\string', '\\string');  //-> true
// or
isSQLMatch(String.raw`\\string`, String.raw`\string`);  //-> true

SELECT '\string' LIKE '\\string';  --> true