SQL-like query language for csv

String Functions

name description
TRIM Return a string with all leading and trailing characters removed
LTRIM Return a string with all leading characters removed
RTRIM Return a string with all trailing characters removed
UPPER Return a string with all characters mapped to their upper case
LOWER Return a string with all characters mapped to their lower case
BASE64_ENCODE Return a base64 encoding of a string
BASE64_DECODE Return a string represented by a base64 encoding
HEX_ENCODE Return a hexadecimal encoding of a string
HEX_DECODE Return a string represented by a hexadecimal encoding
LEN Return the number of characters of a string
BYTE_LEN Return the byte length of a string
WIDTH Return the string width of a string
LPAD Return a string left-side padded
RPAD Return a string right-side padded
SUBSTRING Return the substring of a string
SUBSTR Return the substring of a string using zero-based indexing
INSTR Return the index of the first occurrence of a substring
LIST_ELEM Return a element of a list
REPLACE Return a string replaced the substrings with another string
FORMAT Return a formatted string
JSON_VALUE Return a value from json
JSON_OBJECT Return a string formatted in json object

Definitions

TRIM

TRIM(str)
str
string
return
string

Returns the string value that is removed all leading and trailing white spaces from str.

TRIM(str, charset)
str
string
charset
string
return
string

Returns the string value that is removed all leading and trailing characters contained in charset from str.

LTRIM

LTRIM(str)
str
string
return
string

Returns the string value that is removed all leading white spaces from str.

LTRIM(str, charset)
str
string
charset
string
return
string

Returns the string value that is removed all leading characters contained in charset from str.

RTRIM

RTRIM(str)
str
string
return
string

Returns the string value that is removed all trailing white spaces from str.

RTRIM(str, charset)
str
string
charset
string
return
string

Returns the string value that is removed all trailing characters contained in charset from str.

UPPER

UPPER(str)
str
string
return
string

Returns the string value replaced str with characters mapped to their upper case.

LOWER

LOWER(str)
str
string
return
string

Returns the string value replaced str with characters mapped to their upper case.

BASE64_ENCODE

BASE64_ENCODE(str)
str
string
return
string

Returns the Base64 encoding of str.

BASE64_DECODE

BASE64_DECODE(str)
str
string
return
string

Returns the string value represented by str that is encoded with Base64.

HEX_ENCODE

HEX_ENCODE(str)
str
string
return
string

Returns the hexadecimal encoding of str.

HEX_DECODE

HEX_DECODE(str)
str
string
return
string

Returns the string value represented by str that is encoded with hexadecimal.

LEN

LEN(str)
str
string
return
integer

Returns the number of characters of str.

BYTE_LEN

BYTE_LEN(str [, encoding])
str
string
encoding
string

“UTF8”, “UTF16” or “SJIS”. The default is “UTF8”.

return
integer

Returns the byte length of str.

WIDTH

WIDTH(str)
str
string
return
integer

Returns the string width of str. Half-width characters are counted as 1, and full-width characters are counted as 2.

LPAD

LPAD(str, len, padstr [, pad_type, encoding])
str
string
len
integer
padstr
string
pad_type
string

“LEN”, “BYTE” or “WIDTH”. The default is “LEN”.

encoding
string

“UTF8”, “UTF16” or “SJIS”. The default is “UTF8”.

return
string

Returns the string value of str padded with leading padstr to a length specified by len.

RPAD

RPAD(str, len, padstr [, pad_type, encoding])
str
string
len
integer
padstr
string
pad_type
string

“LEN”, “BYTE” or “WIDTH”. The default is “LEN”.

encoding
string

“UTF8”, “UTF16” or “SJIS”. The default is “UTF8”.

return
string

Returns the string value of str padded with trailing padstr to a length specified by len.

SUBSTRING

SUBSTRING(str FROM position [FOR len])
SUBSTRING(str, position [, len])
str
string
position
integer
len
integer
return
string

Returns the len characters in str starting from the position-th character using one-based positional indexing.

If position is 0, then it is treated as 1.
if len is not specified or len is longer than the length from position to the end, then returns the substring from position to the end.
If position is negative, then starting position is position from the end of the str.

SUBSTR

SUBSTR(str, position [, len])
str
string
position
integer
len
integer
return
string

This function behaves the same as SUBSTRING, but uses zero-based positional indexing.

INSTR

INSTR(str, substr)
str
string
substr
string
return
integer

Returns the index of the first occurrence of substr in str, or null if substr is not present in str.

LIST_ELEM

LIST_ELEM(str, sep, index)
str
string
sep
string
index
integer
return
string

Returns the string at index in the list generated by splitting with sep from str.

REPLACE

REPLACE(str, old, new)
str
string
old
string
new
string
return
string

Returns the string that is replaced all occurrences of old with new in str.

FORMAT

FORMAT(format [, replace_value ... ])
format
string
replace_value
value

Returns the formatted string replaced placeholders with replace in format.

Format Placeholder

%[flag][width][.precision]specifier
flag
flag description
+ Print a plus sign for numeric values
‘ ’ (U+0020 Space) Print a space instead of a plus sign
- Pad on the right
0 Pad with zeros
width
integer

Width of the replaced string.

precision
integer

Number of digits after the decimal point for a float value, or max length for a string value.

specifier
specifier description
b Base 2 integer
o Base 8 integer
d Base 10 integer
x Base 16 integer with lower cases
X Base 16 integer with upper cases
e Exponential notation with lower cases
E Exponential notation with upper cases
f Floating point decimal number
s String representation of the value
q Quoted string representation of the value
i Quoted identifier representation of the value
T Type of the value
% ’%’

Quoted string and identifier representations are escaped for special characters.

JSON_VALUE

JSON_VALUE(json_query, json_data)
json_query
string

JSON Query to uniquely specify a value.

json_data
string
return
value

Returns a value in _json_data.

A JSON values are converted to following types.

JSON value csvq value
object string
array string
number integer or float
string string
true boolean
false boolean
null null

JSON_OBJECT

JSON_OBJECT([field [, field ...]])

field
  : value
  : value AS alias
value
value
alias
identifier
return
string

Returns a string formatted in JSON.

If no arguments are passed, then the object include all fields in the view.