@terascope/data-mate / function-configs/string/sql-utils
function-configs/string/sql-utils
Variables
ALPHA_LOCALES
constALPHA_LOCALES:"^[A-Za-z]+$"='^[A-Za-z]+$'
Defined in: data-mate/src/function-configs/string/sql-utils.ts:14
validator.isAlpha's en-US alphabet, as an RE2 pattern.
Other locales use different letter sets, and several cannot be written as a simple class, so the functions using this handle only the default locale.
ALPHANUMERIC_LOCALES
constALPHANUMERIC_LOCALES:"^[0-9A-Za-z]+$"='^[0-9A-Za-z]+$'
Defined in: data-mate/src/function-configs/string/sql-utils.ts:17
validator.isAlphanumeric's en-US set.
HAS_UNICODE_WORD
constHAS_UNICODE_WORD: "[a-z][A-Z]|[A-Z]{2}[a-z]|[0-9][a-zA-Z]|[a-zA-Z][0-9]|[^a-zA-Z0-9 ]" = '[a-z][A-Z]|[A-Z]{2}[a-z]|[0-9][a-zA-Z]|[a-zA-Z][0-9]|[^a-zA-Z0-9 ]'
Defined in: data-mate/src/function-configs/string/sql-utils.ts:109
lodash's test for which word splitter to use. The case converters run in SQL only when it does not match.
_.camelCase and the other converters run words(deburr(string).replace(/['’]/g, '')), and
words picks one of two algorithms:
var reHasUnicodeWord = /[a-z][A-Z]|[A-Z]{2}[a-z]|[0-9][a-zA-Z]|[a-zA-Z][0-9]|[^a-zA-Z0-9 ]/;
var reAsciiWord = /[^\x00-\x2f\x3a-\x40\x5b-\x60\x7b-\x7f]+/g;
When reHasUnicodeWord does not match, the result is string.match(reAsciiWord), which splits
on ASCII punctuation only, and SQL reproduces that exactly. The other algorithm handles case
transitions, digit/letter boundaries, acronyms like XMLHttpRequest and combining marks, and
keeps the UDF.
deburr does nothing to input this guard admits, which contains only [a-zA-Z0-9 ].
HASH_LENGTHS
constHASH_LENGTHS:Record<string,number>
Defined in: data-mate/src/function-configs/string/sql-utils.ts:44
validator.isHash's length table, copied from its source. The check is only ^[a-fA-F0-9]{N}$.
SQL_HASHES
constSQL_HASHES: readonlystring[]
Defined in: data-mate/src/function-configs/string/sql-utils.ts:209
A hash or byte encoding of a string in SQL, shared by encode and createID.
Returns null for a combination DuckDB cannot express, which is what the callers' applies check.
The results match node:crypto and Buffer byte for byte, including for empty, non-ASCII,
astral and combining-mark input:
md5,sha1andsha256return a lowercase hex digest, matchingcreateHash(...).digest('hex'). There is nosha512orsha384.- A base64 digest is
to_base64(unhex(<digest>)). Buffer.from(x).toString('hex')islower(hex(encode(x))).hex()is uppercase, andencode()is used because a VARCHAR-to-BLOB cast rejects non-ASCII.Buffer.from(x).toString('base64')isto_base64(encode(x)).
Functions
capitalizeSql()
capitalizeSql(
word):string
Defined in: data-mate/src/function-configs/string/sql-utils.ts:134
word[0].toUpperCase() + word.slice(1).toLowerCase(), which is lodash's capitalize.
Parameters
| Parameter | Type |
|---|---|
word | string |
Returns
string
caseConvertSql()
caseConvertSql(
value,options):string
Defined in: data-mate/src/function-configs/string/sql-utils.ts:122
A case conversion over the ASCII word list.
join is the separator between words; first and rest say how each word is cased. An empty
word list gives '', which is what _.camelCase('') and _.camelCase('---') return.
Parameters
| Parameter | Type |
|---|---|
value | string |
options | { first: (word) => string; join: string; rest: (word) => string; } |
options.first | (word) => string |
options.join | string |
options.rest | (word) => string |
Returns
string
defaultLocale()
defaultLocale(
locale):boolean
Defined in: data-mate/src/function-configs/string/sql-utils.ts:20
True when no locale was given, so validator's en-US default applies.
Parameters
| Parameter | Type |
|---|---|
locale | unknown |
Returns
boolean
emptyToNull()
emptyToNull(
list):string
Defined in: data-mate/src/function-configs/string/sql-utils.ts:301
A list expression that returns NULL rather than [] when nothing matched.
extract under global returns null for no match in both modes, never an empty list, while
regexp_extract_all returns [].
Parameters
| Parameter | Type |
|---|---|
list | string |
Returns
string
encodeURIComponentSql()
encodeURIComponentSql(
value):string
Defined in: data-mate/src/function-configs/string/sql-utils.ts:31
encodeURIComponent, built from url_encode plus the five characters it over-escapes.
url_encode percent-escapes !, ', (, ) and *, which encodeURIComponent leaves alone.
Unescaping them afterwards is safe: a literal % in the input is already %25 by then, so a
%21 in the output can only come from a real !.
Parameters
| Parameter | Type |
|---|---|
value | string |
Returns
string
extractMarkerAllSql()
extractMarkerAllSql(
value,start,end):string
Defined in: data-mate/src/function-configs/string/sql-utils.ts:259
extract's marker mode with global: every extraction, as a list.
The repeated scan is a non-greedy (?s).*? between the two escaped markers. (?s) is required
because the scan collects newlines and RE2's . does not match one without it. For example,
'a|b|c|d' gives ['b'] and '<>' gives [''].
Parameters
| Parameter | Type |
|---|---|
value | string |
start | string |
end | string |
Returns
string
extractMarkerSql()
extractMarkerSql(
value,start,end):string
Defined in: data-mate/src/function-configs/string/sql-utils.ts:244
extract's marker mode: the text between the first start and the first end after it.
This mirrors _subSlice, which finds start and then collects characters until end. A second
start before the end is content ('<a<b>' extracts 'a<b'), an unterminated run yields
null, and 'a|b|c' with | for both markers extracts 'b'.
Parameters
| Parameter | Type |
|---|---|
value | string |
start | string |
end | string |
Returns
string
extractRegexAllSql()
extractRegexAllSql(
value,pattern,group):string
Defined in: data-mate/src/function-configs/string/sql-utils.ts:289
extract's regex mode with global: every match, or every first capture group.
Parameters
| Parameter | Type |
|---|---|
value | string |
pattern | string |
group | number |
Returns
string
extractRegexSql()
extractRegexSql(
value,pattern,group):string
Defined in: data-mate/src/function-configs/string/sql-utils.ts:278
extract's regex mode, built from what matchAll does.
matchAll forces the g flag and, per match, collects every capture group if the pattern has
any, or the whole match otherwise. extract then takes the first element, or the whole list
under global. So the SQL is regexp_extract at group 0 or 1, and regexp_extract_all for the
list.
The no-match case needs care. matchAll returns null when nothing matched, and its loop also
stops on an empty match, so a pattern whose first match is empty gives null, not ''.
regexp_extract returns '' for both, which nullif maps to null. When extracting a group, the
whole match is tested first, because a group can capture the empty string inside a non-empty
match.
Parameters
| Parameter | Type |
|---|---|
value | string |
pattern | string |
group | number |
Returns
string
hasHashSql()
hasHashSql(
algo,digest):boolean
Defined in: data-mate/src/function-configs/string/sql-utils.ts:221
Whether hashSql has an expression for this pair, so an applies need not build it.
Parameters
| Parameter | Type |
|---|---|
algo | unknown |
digest | unknown |
Returns
boolean
hashSql()
hashSql(
algo,digest,value):string|null
Defined in: data-mate/src/function-configs/string/sql-utils.ts:211
Parameters
| Parameter | Type |
|---|---|
algo | string |
digest | string |
value | string |
Returns
string | null
isBase64Sql()
isBase64Sql(
value):string
Defined in: data-mate/src/function-configs/string/sql-utils.ts:161
Base64 validation per RFC 4648 with padding, validator.isBase64's default.
var base64WithPadding = /^[A-Za-z0-9+/]+={0,2}$/;
if (str === '') return true;
if (options.padding && str.length % 4 !== 0) return false;
This intentionally does not reproduce core-utils' isBase64, which also requires a UTF-8 round
trip to be lossless. toString('utf8') replaces every invalid byte with U+FFFD, so that check
accepts only base64 of UTF-8 text and rejects most base64-encoded binary.
Parameters
| Parameter | Type |
|---|---|
value | string |
Returns
string
isFQDNSql()
isFQDNSql(
value,udf):string
Defined in: data-mate/src/function-configs/string/sql-utils.ts:182
validator.isFQDN with its default options, for ASCII input.
The function has no TLD list, which is what makes it expressible in SQL. With the defaults
(require_tld, no underscores, no trailing dot, no wildcard, no numeric TLD, max length
enforced) the algorithm is:
- at least two dot-separated parts;
- the last part matches
/^([a-z…]{2,}|xn[a-z0-9-]{2,})$/i, contains no whitespace and is not all digits; - every part is 1-63 characters, matches
/^[a-z_0-9-]+$/i, has no full-width character, and neither starts nor ends with-; and withallow_underscoresfalse, contains no_.
Non-ASCII input keeps the UDF. validator's character classes run to \u{ffff} and include a
full-width exclusion, which is not worth reproducing.
Parameters
| Parameter | Type |
|---|---|
value | string |
udf | (v) => string |
Returns
string
isOneCodePoint()
isOneCodePoint(
value):boolean
Defined in: data-mate/src/function-configs/string/sql-utils.ts:233
Whether a value is a single code point (not a single UTF-16 code unit).
extract's marker mode compares char === start while iterating code points, so a
multi-character marker never matches and an astral one matches as a unit.
Parameters
| Parameter | Type |
|---|---|
value | unknown |
Returns
boolean
phoneNumberLikeSql()
phoneNumberLikeSql(
value):string
Defined in: data-mate/src/function-configs/string/sql-utils.ts:318
isPhoneNumberLike: strip every non-digit and check that 7 to 20 digits remain. It does not use
libphonenumber.
const testValue = toString(input).trim().replace(/\D/g, '');
return inNumberRange(testValue.length, { min: 7, max: 20, inclusive: true });
trim() cannot change the answer because whitespace is not a digit. \D is [^0-9] in both
engines, and the remaining digits are ASCII, so length() counting characters agrees with
JavaScript counting code units.
Parameters
| Parameter | Type |
|---|---|
value | string |
Returns
string
shannonEntropySql()
shannonEntropySql(
value):string
Defined in: data-mate/src/function-configs/string/sql-utils.ts:334
Shannon entropy without an aggregate: split into characters, count each distinct one, and sum the terms.
list_filter over the distinct characters does the per-character counting. It is quadratic in
the number of distinct characters, which for text is bounded by the alphabet rather than the
string length. The result is exact.
The JavaScript builds its frequency table over code points (for (const char of input)) but
divides by input.length, which counts UTF-16 code units. The two differ only for astral input,
so astral input keeps the UDF.
Parameters
| Parameter | Type |
|---|---|
value | string |
Returns
string
upperFirstSql()
upperFirstSql(
word):string
Defined in: data-mate/src/function-configs/string/sql-utils.ts:144
_.upperFirst: the first character uppercased and the rest left as is.
This is not capitalize, which also lowercases the rest. _.startCase uses this one, which is
why startCase('HELLO WORLD') is 'HELLO WORLD' and not 'Hello World'.
Parameters
| Parameter | Type |
|---|---|
word | string |
Returns
string
uuidPattern()
uuidPattern(
version):string|null
Defined in: data-mate/src/function-configs/string/sql-utils.ts:70
Parameters
| Parameter | Type |
|---|---|
version | unknown |
Returns
string | null