String Functions
String Operator and Function will work with text / enum type.
String Operator / Function
By now, ClapDB just support a few operators and functions.
- length
- substr
- lower
- position
- upper
- startswith
- ltrim
- rtrim
- btrim
- concat
- like
- not like
- regexp_like
ClapDB query SQL will fully compatibility with PostgreSQL’s SQL, and string functions and operators’ current state can be checked in below chart.
Compatible with PostgreSQL
According to PostgreSQL String Functions and Operators
| PostgreSQL String Operator / Function | Description | Formula | Example | ClapDB supported | ClapDB will support in future release |
|---|---|---|---|---|---|
| || | Concatenates the two strings. | text || text → text | ’hello’ || ‘world’ → ‘helloworld’ | ||
| || | Converts the non-string input to text, then concatenates the two strings. (The non-string input cannot be of an array type, because that would create ambiguity with the array || operators. If you want to concatenate an array’s text equivalent, cast it to text explicitly.) | text || anynonarray → text | ’hello’ || 2 → ‘hello2’ | ||
| || | anynonarray || text → text | ’hello’ || 2 → ‘hello2’ | |||
| btrim | Removes the longest string consisting only of characters in characters (a space by default) from the start and end of string. | btrim(string text [, characters text]) → text | btrim(‘xyxtrimyyx’, ‘xyz’) → ‘trim’ | ||
| text IS [NOT][form] NORMALIZED | Checks whether the string is in the specified Unicode normalization form. The optional form key word specifies the form: NFC (the default), NFD, NFKC, or NFKD. This expression can only be used when the server encoding is UTF8. Note that checking for normalization using this expression is often faster than normalizing possibly already normalized strings. | text IS [NOT] NORMALIZED [form] → boolean | ’Mnchen’ IS NORMALIZED → true | ||
| bit_length | Returns number of bits in the string (8 times the octet_length). | bit_length(string text) → integer | bit_length(‘jose’) → 32 | ||
| char_length | Returns the number of characters in string. | char_length(string text) → integer | char_length(‘jose’) → 4 | ||
| character_length | character_length(string text) → integer | character_length(‘jose’) → 4 | |||
| lower | Converts the string to all lower case, according to the rules of the database’s locale. | lower(string text) → text | lower(‘TOM’) → ‘tom’ | ||
| lpad | Extends the string to length length by prepending the characters fill (a space by default). If the string is already longer than length then it is truncated (on the right). | lpad(string text, length integer [, fill text]) → text | lpad(‘hi’, 5, ‘xy’) → ‘xyxhi’ | ||
| ltrim | Removes the longest string containing only characters in characters (a space by default) from the start of string. | ltrim(string text [, characters text]) → text | ltrim(‘zzzytest’, ‘xyz’) → ‘test’ | ||
| normalize | Converts the string to the specified Unicode normalization form. The optional form key word specifies the form: NFC (the default), NFD, NFKC, or NFKD. This function can only be used when the server encoding is UTF8. | normalize(string text [, form text]) → text | normalize(U&‘\0061\0308bc’, NFC) → U&‘\00E4bc’ | ||
| octet_length | Returns the number of bytes in string. | octet_length(string text) → integer | octet_length(‘josé’) → 5 | ||
| octet_length | octet_length(character) → integer | octet_length(‘abc ‘::character(4)) → 4 | |||
| overlay | Replaces the substring of string that starts at the start’th character and extends for count characters with newsubstring. If count is omitted, it defaults to the length of newsubstring. | overlay ( string text PLACING newsubstring text FROM start integer [ FOR count integer ] ) → text | overlay(‘Txxxxas’ placing ‘hom’ from 2 for 4) → Thomas | ||
| position | Returns first starting index of the specified substring within string, or zero if it’s not present. | position ( substring text IN string text ) → integer | position(‘om’ in ‘Thomas’) → 3 | ||
| rpad | Extends the string to length length by appending the characters fill (a space by default). If the string is already longer than length then it is truncated. | rpad ( string text, length integer [, fill text ] ) → text | rpad(‘hi’, 5, ‘xy’) → hixyx | ||
| rtrim | Removes the longest string containing only characters in characters (a space by default) from the end of string. | rtrim(string text [, characters text]) → text | rtrim(‘testxxzx’, ‘xyz’) → ‘test’ | ||
| substring | Extracts the substring of string starting at the start’th character if that is specified, and stopping after count characters if that is specified. Provide at least one of start and count. | substring ( string text [ FROM start integer ] [ FOR count integer ] ) → text | substring(‘Thomas’ from 2 for 3) → hom | ||
| substring | Extracts the first substring matching POSIX regular expression | substring ( string text FROM pattern text ) → text | substring(‘Thomas’ from ‘…$’) → mas | ||
| substring | Extracts the first substring matching SQL regular expression; The first form has been specified since SQL:2003; the second form was only in SQL:1999 and should be considered obsolete. | substring ( string text SIMILAR pattern text ESCAPE escape text ) → text | substring(‘Thomas’ similar ‘…$’) → mas | ||
| substring | substring ( string text FROM pattern text FOR escape text ) → text | substring(‘Thomas’ similar ‘…$’) → mas | |||
| trim | Removes the longest string containing only characters in characters (a space by default) from the start, end, or both ends (BOTH is the default) of string. | trim ( [ LEADING | TRAILING | BOTH ] [ characters text ] FROM string text ) → text | trim(both ‘xyz’ from ‘yxTomxx’) → Tom | ||
| upper | Converts the string to all upper case, according to the rules of the database’s locale. | upper ( text ) → text | upper(‘tom’) → TOM |
Pattern Matching
LIKE & ILIKE
LIKE operator is supported by ClapDB, and the syntax is the same as PostgreSQL. it support the following wildcards:
- _ : matches any single character
- % : matches any sequence of characters
ILIKE is similar to LIKE but is case insensitive.
examples:
SELECT * FROM table WHERE column LIKE 'a%';SELECT * FROM table WHERE column LIKE 'hello_';SELECT * FROM table WHERE column LIKE 'a%b';regex
Operators
| PostgreSQL Operator | Description | Example | ClapDB supported | ClapDB will support in future release |
|---|---|---|---|---|
| ~ | String matches regular expression, case sensitively | ’thomas’ ~ ‘t.*ma’ | ||
| ~* | String matches regular expression, case insensitively | ’thomas’ ~* ‘T.*MA’ | ||
| !~ | String does not match regular expression, case sensitively | ’thomas’ !~ ‘t.*ma’ | ||
| !~* | String does not match regular expression, case insensitively | ’thomas’ !~* ‘T.*MA’ |
pattern matching support PCRE (Perl Compatible Regular Expressions) syntax.
Functions
| PostgreSQL Function | Description | Formula | Example | ClapDB supported | ClapDB will support in future release |
|---|---|---|---|---|---|
| regexp_count | The regexp_count function counts the number of places where a regular expression pattern matches a string. | regexp_count(string, pattern [, start [, flags ]]) -> integer | regexp_count(‘ABCABCAXYaxy’, ‘A.‘) → 3 | ||
| regexp_instr | The regexp_instr function returns the starting or ending position of the N’th match of a regular expression pattern to a string, or zero if there is no such match. | regexp_instr(string, pattern [, start [, N [, endoption [, flags [, subexpr ]]]]]) -> integer | regexp_instr(‘ABCABCAXYaxy’, ‘A.’, 1, 2) → 4 | ||
| regexp_like | The regexp_like function checks whether a match of a regular expression pattern occurs within a string, returning boolean true or false. | regexp_like(string, pattern [, flags ]) → boolean | regexp_like(‘ABCABCAXYaxy’, ‘A.‘) → true | ||
| regexp_matches | The regexp_match function returns a text array of matching substring(s) within the first match of a regular expression pattern to a string. | regexp_matches(string, pattern [, flags ]) → text[] | regexp_matches(‘foobarbequebaz’, ‘bar.*que’) → {barbeque} | ||
| regexp_replace | The regexp_replace function provides substitution of new text for substrings that match regular expression patterns. | regexp_replace(string, pattern, replacement [, flags ]) → text | regexp_replace(‘foobarbequebaz’, ‘bar.*que’, ‘123’) → foo123baz | ||
| regexp_substr | The regexp_substr function returns the substring that matches a regular expression pattern, or NULL if there is no match. | regexp_substr(string, pattern [, start [, nth [, flags ]]]) → text | regexp_substr(‘number of your street, town zip, FR’, ’[^,]+’, 1, 2) -> town zip |
SIMILAR TO
not supported by ClapDB.